topic Re: Dollar Sign Expansion in Set Analysis in QlikView

Dollar Sign Expansion in Set Analysis

Nicole-Smith — Tue, 21 May 2013 12:46:50 GMT

I've never been able to get this work the way I want it to, so I've always found a workaround, but now I'm really wondering what I need to do to make it work.

Say I have a bunch of stuff that is scored on a monthly basis. If I look at it by month, I see the score for that month; however, if I look at it by quarter, I want to see the score for the latest month in that quarter.

I would expect that the set analysis in the following expression would work for this:

only({<Month = {'$(=max(Month))'}>} Score)

However, this expression returns the Score for the latest Month in all of the data, not the latest Month of the quarter (if my data is from Jan 2012 to Dec 2012, it only returns the Dec 2012 data, not Mar 2012, Jun 2012, Sep 2012, and Dec 2012).

Can anybody please help me out with this? I hope it's something easy that I'm just overlooking...

Thanks for your help!

Re: Dollar Sign Expansion in Set Analysis

Nicole-Smith — Tue, 21 May 2013 12:50:06 GMT

I forgot to mention that Quarter is a Dimension on the chart for this.

Re: Dollar Sign Expansion in Set Analysis

CELAMBARASAN — Tue, 21 May 2013 13:21:15 GMT

Are you using MonthName to create Month field.

Try with this

Sum({<Month={"=Month=MonthName(QuarterEnd(Month))"}>} Score)

If possible try to create a flag to determine QuarterEnd month in script itself.

Re: Dollar Sign Expansion in Set Analysis

Gysbert_Wassenaar — Tue, 21 May 2013 13:32:54 GMT

Getting this to work with set analysis is more trouble than it's worth. You'll need an if statement (or pick-match combination) to create a different set for each quarter. Far easier is to add a table in the script

Report:

load Quarter as ReportQuarter, max(Month) as Month

resident ...tablewithmonthsandquarters...

group by Quarter;

Then you can use ReportQuarter instead of Quarter in your chart.

Re: Dollar Sign Expansion in Set Analysis

Nicole-Smith — Tue, 21 May 2013 13:51:26 GMT

Thank you all for your responses!