Conditional Count in straight table

[kishorj1982]

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.

[swuehl · Accepted solution]

You can look into using advanced aggregation, like

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

see also attached

[kishorj1982]

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?

[swuehl]

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

Pitfalls of the Aggr function

[kishorj1982]

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

[kishorj1982]

Hello,

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

[swuehl]

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.