Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis and expression scope

I am attempting to show the sum of values purely for the last day of any given month. When I try

sum({($<[Financial year] = {$(=Max([Financial year]))},Day={$(=day(MonthEnd(MakeDate(if(num(Month)< FYFirstMonth,max([Financial year]),max([Financial year]) - 1),num(Month)))))}>)} [Month end balance])


I get nothing. If I try the Day expression as a value on the axis rather than part of a set expression, it shows the correct values:

day(MonthEnd(MakeDate(if(num(Month)< FYFirstMonth,max([Financial year]),max([Financial year]) - 1),num(Month))))


. I have managed to frig the set by hardcoding:

sum({($<[Financial year] = {$(=Max([Financial year])-1)},Day={28, 29, 30, 31}>)} distinct [Month end balance])


but that is profoundly unsatisfactory, and then if there are values on more than one of the last four days of a 31 day month it will skew the result. Is there any way for the first expression to work?

0 Replies