topic Set analysis returns unexpected results in QlikView

Set analysis returns unexpected results

agilos_mla — Fri, 04 Mar 2011 21:44:31 GMT

Hi guys, I need your help on this interesting case. I've got this data set :

Year	Date	Amount
2009	31/12/2009	912
2010	30/06/2010	1006
2010	30/09/2010	1009
2011	31/01/2011	1101
2011	28/02/2011	1102

I would like to get into a pivot chart , the year, the max date founded for each year and the value related to this max date, whatever the selection. It will look like :

Year	Max({1}Date)	Expression
2009	31/12/2009	912
2010	30/09/2010	1009
2011	28/02/2011	1102

I encounters some issues to resolve the Expression . I tried :

sum( {1<Date={'$(=Date(Max(Date)))'}>} Amount)

it returns the right value for year 2011 but 0 for previous years. What is missing in this set analysis to obtain the desired results or another way to solve the problem without getting back bo the script?

Thx,

Michael

Set analysis returns unexpected results

Fri, 04 Mar 2011 22:32:28 GMT

Hi Michael, try this:

For the max date -> date(max(Date)

For the value associated -> firstsortedvalue(Amount,-Date)

Set analysis is not going to work in this case, since it is evaluated once per chart and not for every value within the domension.

Regards

Set analysis returns unexpected results

johnw — Fri, 04 Mar 2011 22:48:59 GMT

Ivan's solution should work if you don't actually need a sum, though you'll probably want to add a {1} to it if I understood your question.

If you DO need the sum, maybe this?

sum({1<Date={'$(=concat({1} aggr(max({1} Date),Year),chr(39) & ',' & chr(39)))'}>} Amount)

Yes, set analysis is only evaluated once per chart, but with the expression above (if I got it right), we generate a list of all the dates of interest. Only ONE of those dates will match each line in your chart, so everything will come out as expected in the end.

Set analysis returns unexpected results

Fri, 04 Mar 2011 22:55:58 GMT

Yes John is right, in case you need the sum of amount's that will be the expression to use, although I think there is missing a date conversion for the expression to work properly, like this:

sum({1<Date={'$(=concat({1} aggr(date(max({1} Date)),Year),chr(39) & ',' & chr(39)))'}>} Amount)

Regards

Set analysis returns unexpected results

agilos_mla — Sat, 05 Mar 2011 07:47:27 GMT

Both Solutions works great !

I'm going to use the one with the sum since I get just a sample of the data, there might be multiple value ifor the Mac Date that must be summed probably in this case.

Thx guys,

Michael