Set analysis and expression scope

Thu, 07 Jan 2010 13:39:18 GMT

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?

topic Set analysis and expression scope in QlikView

Set analysis and expression scope