5 Replies Latest reply: Jul 31, 2012 2:02 AM by Sunil Chauhan

# 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

• ###### Re: Urgent help needed with set analysis in expression

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

• ###### Re: Urgent help needed with set analysis in expression

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

• ###### Re: Urgent help needed with set analysis in expression

for better help you can attach  a sample file

hope this helps

• ###### Re: Urgent help needed with set analysis in expression

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

• ###### Re: Urgent help needed with set analysis in expression

why not you make make date in script

MakeDate(Year, Month, Day) as Datefield

and use that field in same expression

hope this helps