topic Re: Set analysis expression to exclude Date selection in QlikView

Set analysis expression to exclude Date selection

ramyasaiqv — Wed, 30 Aug 2017 04:42:07 GMT

Sum(If(YYYYMM=Max(total {<[Amount]={"=Len(trim([Amount] ))>0"}>} YYYYMM), Amount,

If(mod(MonthNum,3)=0 , Amount)))/Count(distinct TOTAL [item])

I have this expression and is used in chart that works fine. I have a list box with “Date” and I want the above expression should be independent of “Date” selection

I’m struggling to include ({$<[Date]= >} in the above expression so that the expression will be independent of “Date” selection. Please can someone help me in writing the correct set expression . Thank you,

Ramya

Re: Set analysis expression to exclude Date selection

techvarun — Wed, 30 Aug 2017 04:46:07 GMT

May be?

Sum(If(YYYYMM=Max(total {<[Amount]={"=Len(trim([Amount] ))>0"}, Date = {'*'}>} YYYYMM), Amount,

If(mod(MonthNum,3)=0 , Amount)))/Count(distinct TOTAL [item])

Re: Set analysis expression to exclude Date selection

tresB — Wed, 30 Aug 2017 05:04:27 GMT

Try incorporating the date selection ignorance (using set) in all the aggregation functions, like:

Sum( {<Date>} If(YYYYMM=Max(total {<Date, [Amount]={"=Len(trim([Amount] ))>0"}>} YYYYMM), Amount,

If(mod(MonthNum,3)=0 , Amount)))/Count(distinct TOTAL {<Date>} [item])

Re: Set analysis expression to exclude Date selection

its_anandrjs — Wed, 30 Aug 2017 07:14:39 GMT

Try with

Sum(If(YYYYMM=Max(total {<[Amount]={"=Len(trim([Amount] ))>0"},DateField=>} YYYYMM), Amount,

If(mod(MonthNum,3)=0 , Amount)))/Count(distinct TOTAL [item])

Re: Set analysis expression to exclude Date selection

ramyasaiqv — Wed, 30 Aug 2017 14:04:16 GMT

It worked. Thank you Tesesco.

Re: Set analysis expression to exclude Date selection

ramyasaiqv — Wed, 30 Aug 2017 14:05:07 GMT

Thank you for the response Anand.

Re: Set analysis expression to exclude Date selection

ramyasaiqv — Wed, 30 Aug 2017 14:05:33 GMT

Thank you for the response Varun.