topic Re: Help set analysis in QlikView

Help set analysis

Anonymous — Mon, 18 Feb 2013 09:09:37 GMT

Hi all,

for each three-month period I have 3 possible date: 01/31, 02/29, 03/30.

Without filtering data I would have sum(VALORE_MAG) for the max(date) of each period (year, threemonth period)

DATA INVENTARIO= date

ANNO= year

TRIMESTRE= three month period

This expression is not correct because it extracts the maximum date throughout the period, not the quarter

sum({<[DATA INVENTARIO]={'$(=Max([DATA INVENTARIO]))'}, ANNO,TRIMESTRE>}VALORE_MAG)

for each T# i would like to have a value (valore trim mag) that is the value for the max(date) of the quarter.

How can I do that without filter data?

Thank you to all!

alexandros17 — Mon, 18 Feb 2013 09:22:26 GMT

Try to aggr data by Anno and trimestre before sum ...

Anonymous — Mon, 18 Feb 2013 09:44:02 GMT

Thanks, how can i write it?

aggr(sum({<[DATA INVENTARIO]={'$(=Max([DATA INVENTARIO]))'}>}VALORE_MAG),ANNO,TRIMESTRE)

doesn't work...

vincent_ardiet — Mon, 18 Feb 2013 10:54:52 GMT

Hi,

Try this (if the quarter is in the dimension of your chart) :

sum ({$<DATA INVENTARIO={'$(=concat(aggr(max(DATA INVENTARIO),MESE),chr(39)&','&chr(39)))'}>} VALORE_MAG )

Regards,

Vincent

Anonymous — Mon, 18 Feb 2013 14:36:44 GMT

It works! Thank you!!!

Could you explain me what do you do with $(=concat(aggr(max(DATA INVENTARIO),MESE),chr(39)&','&chr(39)))'?

vincent_ardiet — Mon, 18 Feb 2013 15:23:39 GMT

As you have quarters in dimensions, aggr(max(DATA INVETARIO), MESE) will return 3 values, the last day of each month of the current quarter.

Then, I concatenate the 3 values but I want them to be separated by a single quote (ascii code 39), a coma (',') and another quote.

So I'm composing a string like 'month1','month2','month3' (the opening and closing quotes are before the $ and before the }).

Regards,

Vincent