topic Re: AGGR split out by month in QlikView

AGGR split out by month

danielle_v — Sat, 16 Nov 2024 01:11:45 GMT

Hello,

I'm putting together a measure around on-time performance, aggragated by mode of transport

avg(aggr(
(count({$<Year={'$(vCurrentYear)'}, Month={"<=$(=max(Month))"}, Status = {"On-Time"}>} distinct(OnTimeRef))/
count({$<Year={'$(vCurrentYear)'}, Month={"<=$(=max(Month))"}, Status = {"On-Time", "Late"}>} distinct(OnTimeRef))), Mode))

The above expression is giving me the YTD on-time percentage based on the Month & Year selections I make from the calendar, and is workgin fine.

However, when I put the same expression into a chart split out by Month, it doesn't work and I just get a result for one month. It seems to be an issue with the combination of AGGR and splitting the data out by the month dimension, but I'm not sure what/why. I've tried adding Month into the AGGR condition, but it doesn't seem to make a difference.

Any guidance would be much appreciated!

Re: AGGR split out by month

qliksus — Wed, 26 Feb 2020 10:44:11 GMT

Try this

rangesum(above( count({$<Year={'$(vCurrentYear)'}, Status = {"On-Time"}>} distinct(OnTimeRef))/
count({$<Year={'$(vCurrentYear)'}, Status = {"On-Time", "Late"}>} distinct(OnTimeRef))), Mode) ,0, rowno()) )

Re: AGGR split out by month

danielle_v — Wed, 26 Feb 2020 15:54:16 GMT

Hello,

Thanks for your help, however this solution doesn't seem to work

Re: AGGR split out by month

qliksus — Thu, 27 Feb 2020 03:08:09 GMT

Can you attach the sample file to understand more on the requirement ?

Re: AGGR split out by month

danielle_v — Wed, 04 Mar 2020 09:44:15 GMT

Hi Qliksus,

Thank you for taking the time to reply!

I've attached a file with some dummy data loaded in. I've added text boxes explaining which parts are working and where the issue lies.

Re: AGGR split out by month

qliksus — Fri, 06 Mar 2020 11:38:26 GMT

Sorry for the delayed response man . Just create a group table in the backend like the below

for I =1 to 4
load
$(I) as Monthgroup,
if( ( 1+ IterNo()-1)<=$(I),$(J)+ IterNo()-1) as Month

AutoGenerate(1)
While (1+ IterNo()-1) <= 4;
NEXT

in the front end use the bellow

Dimension: Monthgroup

Expression: avg(aggr(
(count({$<
Year={'$(vCurrentYear)'},
Month=,
Status = {"On-Time"}>}
distinct(OnTimeRef))
/
count({$<
Year={'$(vCurrentYear)'},
Month=,
Status = {"On-Time", "Late"}>}
distinct(OnTimeRef))), Monthgroup,Mode))

Re: AGGR split out by month

alex00321 — Fri, 06 Mar 2020 12:35:10 GMT

I made little update to let it work...

Re: AGGR split out by month

Brett_Bleess — Thu, 26 Mar 2020 15:43:13 GMT

Danielle, did Qliksus' or Alex's posts get you what you needed? If so, please be sure to circle back to the thread to close it out by using the Accept as Solution button on the post(s) that helped you get things working. If you did something different, you can post that and then mark that as the solution, but we appreciate it when Members do properly close out their threads, as this gives credit to the posters and lets the other Members know what actually worked.

Regards,
Brett