Pivot table to retrieve Values with a count > 2

[sibrulotte]

Hi,

I have 3 dimensions in a pivot table, Type, Supplier, month.

I have a set analysis to count distinct unique keys in my expression.

=count({<[Description type transaction] ={'T-DS*','T-DL*'}, Ind_annule -={'1'}>}distinct [AK Aux])

How can I get a list of suppliers by month that have more than one "AK_Aux" per month?

[sibrulotte]

btw, right now I am using

=if(count({<[Description type transaction] ={'T-DS*','T-DL*'}, Ind_annule -={'1'}>}distinct [AK Aux])>1,count({<[Description type transaction] ={'T-DS*','T-DL*'}, Ind_annule -={'1'}>}distinct [AK Aux]),0)

with supress zero values.

not very gracefull

[johnw]

I don't have a more elegant solution, but I'm curious to see if someone does.

The only very minor simplification I'm thinking of is don't have an else value of zero. Then it would return null, and you'd surpress the row with two fewer characters in your expression and one fewer checkboxes modified from default. Still not elegant.

You can make the repeated expression a variable, and reference the variable twice. I don't do that, though. I just like the repeats up under each other so it's very easy to visually confirm that they're exactly the same.

Also not elegant, but you could move the logic to script and have a field [AK Aux T-DS* TDL* not ind_annule 1] or some better name for the field. Then you're using if(count(distinct [AK Aux T-DS* TDL* not ind_annule 1])>1,count(distinct [AK Aux T-DS* TDL* not ind_annule 1])). But it still has what I consider to be the inelegant part, which is repeating the count.

Internally, QlikView will only count once. It's smart enough to know that the two counts will return the same value and just grab the value from the first time. But that doesn't make the expression look any better.