topic Re: Could I use Set Analyis in Count expression? in QlikView

Could I use Set Analyis in Count expression?

Tue, 16 Oct 2012 15:22:34 GMT

Qlikview Experts....

Using Qlikview version 9.

I am using the following expression in a pivot table in lieu of statistics box object, as my requirement is this conditional count.

Please do let me know if there is any other object I can use. This following expression is not giving me the conditional count.

=count(distinct if((sum({<YEARQUARTER={$(=Max(YEARQUARTER))}>} (ZIPDIST/1000) * CALCRATE)/13)/ZIPDIST < BaseAvgWeeklyRevenueperHH, ZIP))

BaseAvgWeeklyRevenueperHH is input value as variable.

Appreciate your time.

Thank you,

Pad.

Re: Could I use Set Analyis in Count expression?

hic — Tue, 16 Oct 2012 15:43:25 GMT

Yes you can use Set Analysis in a Count function. But you cannot have one aggregation (Sum) inside another (Count) the way you do here.

Further, I suspect you should have either the if-function inside the Count() or the set expression. But not both.

HIC

Re: Could I use Set Analyis in Count expression?

Tue, 16 Oct 2012 19:56:54 GMT

Thnak you!

Now..

In my main pivot table i am displaying rows depending on meeting this cutoff condiiton.

if((sum({<YEARQUARTER={$(=Max(YEARQUARTER))}>} (ZIPDIST/1000) * CALCRATE)/13)/ZIPDIST < BaseAvgWeeklyRevenueperHH

I will need to show another peice of info separately - justshowing the count of rows met this condiiton.

Any ideas on how i can approach this?

Thank you,

Pad

Re: Could I use Set Analyis in Count expression?

hic — Tue, 16 Oct 2012 20:39:03 GMT

If the goal is to count the number of rows - or rather, count the number of distinct dimensional values that fulful this demand, then you have a two-step aggregation and need to use the aggr function. Hence

Count( Aggr( if( Sum(...) < BaseAvgWeeklyRevenueperHH, Sum(...) ), <Dimension> ) )

But you need to replace the "BaseAvgWeeklyRevenueperHH" with the aggregation that calculates this number. Further, you divide by ZIPDIST outside the aggregation (the sum function). This only works if ZIPDIST only has one single value. Put it inside the Sum instead.

HIC