Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
chauhans85
Esteemed Contributor

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

Not applicable

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

chauhans85
Esteemed Contributor

Re: Urgent help needed with set analysis in expression

for better help you can attach  a sample file

hope this helps

Not applicable

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

chauhans85
Esteemed Contributor

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

Community Browser