topic Help Expression in QlikView

Help Expression

Wed, 29 Jan 2014 22:12:11 GMT

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

Re: Help Expression

Wed, 29 Jan 2014 23:43:54 GMT

Hi,

You can try

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

Hope it helps.

Re: Help Expression

jagan — Thu, 30 Jan 2014 02:17:59 GMT

Hi,

Try like this

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

Regards,

Jagan.

Re: Re: Help Expression

Thu, 30 Jan 2014 20:52:59 GMT

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;

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

Re: Re: Help Expression

Thu, 30 Jan 2014 21:54:50 GMT

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.

Re: Re: Help Expression

Sat, 01 Feb 2014 19:46:21 GMT

Thank you so much for your help!