Help Expression

[#1]

Hi! I'm new to qlikview.

I'm trying to write an expression like this:

count(aggr(sum({<Year={2013}>}[days])>180),({<type={'O','Y'}>}[name]) ))

I want to count the name who has more than 180 days of work in 2013, and the "Type" field contains "O" or "Y" value.

Any suggestions greatly appreciated.  Andrea

[#2]

Hi,

You can try

count({<type={'O','Y'}>} distinct if(aggr(count({<Year={2013}>}[days]),[name])>180, [name]))

Hope it helps.

[jagan]

Hi,

Try like this

Sum({<type={'O','Y'}>} aggr(If(sum({<Year={2013}>}[days])>180, 1), [name]))

Regards,

Jagan.

[#4]

Hi! Thank you for your help!

I'm trying to use this expression in a pivot table. Is it possible to consider the condition "more than 180 days" with these options?:

1) where one record contain more than 180 days;

or

2) where the sum of records with the same name is more than 180 days;

You can find an example attached.

I really appreciate any help you can provide.

Andrea

[#5 · Accepted solution]

Hi,

My first proposal didn't work; now got it working with set analysis.

Current expression in sample is:

Sum({<Type={'O','Y'}>} aggr(If(sum({<Year={2013}>}[Days Work])>180, 1), [Name]))

It works but is limited to your current selection and pivot table dimensions: if you select type 'O', there's only 1 name matching the condition.

This can be translated in set analysis by:

=Count({<Type={'O','Y'},[Name]={"=Sum({<Year={2013}>}[Days Work])>180"}>} DISTINCT [Name])

If you want to keep the result disregarding your current selection, you can use:

=Count({1<Type={'O','Y'},[Name]={"=Sum({1<Year={2013}>}[Days Work])>180"}>} DISTINCT [Name])

These 3 can be used in pivot tables, but the result will be limited to the dimensions of your pivot table: if you put Type as a dimension, it will give you 1 for 'O' and 1 for 'Y'.

To have it regardless of the dimension (but not the selection) you can use:

=Count({<Year={2013},[Name]={"=Sum({<Year={2013}>}[Days Work])>180"},Type={'O','Y'}>}

          DISTINCT TOTAL [Name])

To have it regardless of both selection and dimension, you can use:

=Count({1<Year={2013},[Name]={"=Sum({1<Year={2013}>}[Days Work])>180"},Type={'O','Y'}>}

          DISTINCT TOTAL [Name])

Hope this helps better.

[#6]

Thank you so much for your help!