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: 
Not applicable

Urgent help needed with set analysis in expression

I have 2 reports: One by Month Year as dimension and other using set analysis to show the latest monthyear data.

1. pivot report by month-year as dimension:

where one of my expression is max(aggr(sum({< PositionType={Resource} > } EN_TOTAL_MW_DA_15min), Year, Month, Day, HE)) and gives the right result which for Mar 2012 should be 3587.

2. Using set analysis to show the latest monthyear data(assuming Mar for now):  doing the set analysis and using the expression as below, do not get the same results and need help with the below expression to give the same result as above.

num(max(aggr(sum({ < Year={$(=Year(Date(max(MakeDate(Year, Month, Day)), 'YYYY')))}, Month={$(=Month(Date(max(MakeDate(Year, Month, Day)), 'MMM')))}, PositionType={Resource} > }EN_TOTAL_MW_DA_15min), Year, Month, Day, HE)), '#,##0')

Am I doing anything wrong as for some reason the aggr dosn't work and do not even get any results.

Attached please find the screen shot, the numbers in red needs to match with that as green.

Any help with this would be greatly appreciated.

Thanks,

DD

5 Replies
SunilChauhan
Champion
Champion

try below code hope this helps

max(aggr(num(sum({ < Year={$(=Year(Date(max(MakeDate(Year, Month, Day)), 'YYYY')))}, Month={$(=Month(Date(max(MakeDate(Year, Month, Day)), 'MMM')))}, PositionType={Resource} > }EN_TOTAL_MW_DA_15min),'#,##0'), Year, Month, Day, HE))

Sunil Chauhan
Not applicable
Author

Thanks Sunil for prompt reply, but for some reason tried different things but aggr doesn't work. There's no data displayed for that column and row.

Not sure what would be causing it or is it aggr function.

Thanks,

DD

SunilChauhan
Champion
Champion

for better help you can attach  a sample file

hope this helps

Sunil Chauhan
Not applicable
Author

The file is too big to share, what's he alternative reduced data file is okay

Got an error: File 'QlikView_testsample.qvw' is too large.

Thanks,

DD

SunilChauhan
Champion
Champion

why not you make make date in script

MakeDate(Year, Month, Day) as Datefield

and use that field in same expression

hope this helps

Sunil Chauhan