Question
Macquarie
IN
Last activity: 3 Oct 2019 14:28 EDT
Can we fetch a report for 2 different dates and group by both the reports and get count and compare the counts?
I need to fetch same report 2 times, for two different dates and then compare the count of both the reports using group by.
For example, if for a combination of region, jurisdiction and gender, we have count today as 500 and count for same combination yesterday was 700, the we need to compare both these counts and show all these report columns and count and comparison values (count today ~ count yesterday) on UI in table format.
Pegasystems Inc.
IN
You can fetch the same report 2 times if you pass the date parameter from section to a Report Definition. Since the aggregator is the same(Group by, Count), you can display the same in section.
Macquarie
IN
How do I compare the count of both the reports and display a new column for that difference in the UI?
Pegasystems Inc.
IN
Okay, first are you able to get the count of both the reports?
Macquarie
IN
yes
Pegasystems Inc.
US
It sounds like you need to create a function alias that does a CASE...WHEN that has a value of 1 if the case date equals the input date and 0 if it doesn't. There may even be an OOTB function alias that already does this.
Then you would create a single summary report with 2 aggregated columns and 3 unaggregated (group by) columns.
Group By columns:
Region
Jurisdiction
Gender
Aggregated columns:
COUNT of function_alias( date1)
COUNT of function_alias( date2)