topic Re: Urgent help needed with set analysis in expression in QlikView

Urgent help needed with set analysis in expression

Mon, 30 Jul 2012 13:59:27 GMT

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,

Re: Urgent help needed with set analysis in expression

SunilChauhan — Mon, 30 Jul 2012 14:04:29 GMT

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

Mon, 30 Jul 2012 14:37:24 GMT

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,

Re: Urgent help needed with set analysis in expression

SunilChauhan — Mon, 30 Jul 2012 14:42:47 GMT

for better help you can attach a sample file

hope this helps

Re: Urgent help needed with set analysis in expression

Mon, 30 Jul 2012 15:14:23 GMT

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,

Re: Urgent help needed with set analysis in expression

SunilChauhan — Tue, 31 Jul 2012 06:02:31 GMT

why not you make make date in script

MakeDate(Year, Month, Day) as Datefield

and use that field in same expression

hope this helps