topic Re: Ignore Time selection in aggr function in QlikView

Ignore Time selection in aggr function

Mon, 03 Nov 2014 14:22:48 GMT

Hi,

A bit of a brain freeze over here.

I need the below expression to ignore the time selection. Where do i put the

{ $ <Time60 = > } or similar?

SUM(Aggr(IF(SettlementOrder= (MIN(TOTAL <Date> SettlementOrder)), Sum(Balance)), Date, SettlementOrder))

Thanks in advance,

Olle

Re: Ignore Time selection in aggr function

JonnyPoole — Mon, 03 Nov 2014 14:24:06 GMT

Put it in ALL chart aggregation functions (in this case sum() and min() ) . Assuming all other syntax is accurate...

change:

SUM(Aggr(IF(SettlementOrder= (MIN(TOTAL <Date> SettlementOrder)), Sum(Balance)), Date, SettlementOrder))

to:

SUM( { $ <Time60 = > } Aggr(IF(SettlementOrder= (MIN( { $ <Time60 = > } TOTAL <Date> SettlementOrder)), Sum( { $ <Time60 = > } Balance)), Date, SettlementOrder))

Re: Ignore Time selection in aggr function

Mon, 03 Nov 2014 15:07:53 GMT

Hi Jonathan,

yes the original works fine.

I tried your tweak but only receiving zero-values

Kind Regardfs,

Olle

Re: Ignore Time selection in aggr function

MK_QSL — Mon, 03 Nov 2014 15:08:30 GMT

Provide your sample data or apps

Re: Ignore Time selection in aggr function

JonnyPoole — Mon, 03 Nov 2014 16:28:29 GMT

If you can't share the QVW i would try breaking apart the expression and putting those parts in new expressions.

Start with these:

MIN( { $ <Time60 = > } TOTAL <Date> SettlementOrder))

Sum( { $ <Time60 = > } Balance)

and then add start to wrap in the rest of it...

IF(SettlementOrder= (MIN( { $ <Time60 = > } TOTAL <Date> SettlementOrder)), Sum( { $ <Time60 = > } Balance))

post which part is failing...

Re: Ignore Time selection in aggr function

Tue, 04 Nov 2014 08:13:38 GMT

Solved my problem using the FirstSortedValue function instead, but thanks for your help!

Kind REgards,

Olle

Re: Ignore Time selection in aggr function

Tue, 04 Nov 2014 09:45:46 GMT

Actually the two of them works perfect when used separately.

But when I put them together I receive null values.

Re: Ignore Time selection in aggr function

JonnyPoole — Tue, 04 Nov 2014 21:32:23 GMT

hi - i wasn't 100% sure on the feedback. I think you mentioned that both the Sum() and the Min( TOTAL <Date> ) work as seperate expressions with the SET ANALYSIS statement but no when you do the If ().

If that is the case you could try wrapping SettlementOrder in the Only() function and putting the SET statement in there too... but i'm not 100% it will work. Really need to see a sample QVW

IF( Only({ $ <Time60 = > } SettlementOrder) = (MIN( { $ <Time60 = > } TOTAL <Date> SettlementOrder)), Sum( { $ <Time60 = > } Balance))