topic Re: Conditional Count in straight table in QlikView

Conditional Count in straight table

kishorj1982 — Sat, 16 Jan 2016 02:47:31 GMT

Hi All,

I have a situation where i need to count no of weeks based on the Sales values provided by user using slider object. If sales values aggregated on week are more than the thrshold set by user, then count those weeks else dont. it seems simple but when I am not using the wk dimension (and I dont want that in the straight table chart) then its not appropriate with end results.

Please note that I have set analysis used for all the aggregations - hence I simulated it in the sample data as well.

Please go through the QVW attached. I just want to achieve similar "wk count" results mentioned in the " Test with Wk" chart

when I am not using the wk dimension i.e. in the "Test without Wk" chart.

Please look into it.

Thanks in advance.

Re: Conditional Count in straight table

swuehl — Sat, 16 Jan 2016 12:08:47 GMT

You can look into using advanced aggregation, like

=Sum( Aggr( if(sum({<[Flg]={1}>} Sale) > $(vSales),count(Wk)), A, B, C, D, Wk, Flg))

Re: Conditional Count in straight table

kishorj1982 — Sat, 16 Jan 2016 14:01:08 GMT

This is exactly I wanted !!!!!

Thank you very much swuehl!!! Can you please explain me the expression and how it works as there are many instances I need to use it and whats the concept of developing these expressions?

Re: Conditional Count in straight table

swuehl — Sat, 16 Jan 2016 17:14:25 GMT

You can start with HIC's blog post and the linked documents:

Pitfalls of the Aggr function

Re: Conditional Count in straight table

kishorj1982 — Sat, 30 Jan 2016 16:18:28 GMT

Hi Swuehl,

I have tried above expression to modify it with pure set expression to get rid off the IF condition. This is to make sure performance improvements.

The new expression I build is :

=sum(aggr(count({<wk={"(sum(Sale) > $(vSales)"}>}DISTINCT Wk),A,B,C,D,Wk))

But somehow its not working as per expectations and the comparison based on the slider values are not in effect, Can you please look into it?

Thanks

Re: Conditional Count in straight table

kishorj1982 — Fri, 05 Feb 2016 01:41:28 GMT

Hello,

Can someone please confirm on the expression I am trying to get in set Analysis ? Whats wrong?

Re: Conditional Count in straight table

swuehl — Fri, 05 Feb 2016 17:37:35 GMT

The set analysis is evaluated once per chart, not per chart or aggr() dimension value, but I think that's what you are assuming here.