QlikView App Dev

[danielle_v]

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!

[qliksus]

Try this 

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

[danielle_v]

Hello,

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

[qliksus]

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

[danielle_v]

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.

[qliksus]

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))

[alex00321]

I made little update to let it work...

[Brett_Bleess]

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