Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danielle_v
Creator
Creator

AGGR split out by month

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!

 

 

 

 

Labels (6)
7 Replies
qliksus
Specialist II
Specialist II

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
Creator
Creator
Author

Hello,

 

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

qliksus
Specialist II
Specialist II

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

danielle_v
Creator
Creator
Author

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
Specialist II
Specialist II

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
Creator II
Creator II

I made little update to let it work...

Brett_Bleess
Former Employee
Former Employee

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.