topic Re: Count dates with condition in QlikView

Count dates with condition

Anonymous — Tue, 19 Aug 2014 06:08:17 GMT

Good day!

There is pivot table TASK in attached model with quantity by Spoints and Dates.

My task is to calculate count of dates by Spoints, which have quantity>60 % from avg(sum( total <Spoint> Quantity))

Thank you!

Re: Count dates with condition

jagan — Tue, 19 Aug 2014 06:16:48 GMT

Hi,

Try this expression

sum( total <Spoint> Quantity)/count({<Quantity={'>0'}>} total <Spoint> Date)

If not working then let me know your expected output.

Regards,

Jagan.

Re: Count dates with condition

jonathandienst — Tue, 19 Aug 2014 06:41:28 GMT

Is this what you are after?

Count({<Quantity = {'>$(=Avg({1} Aggr(Sum({1} Quantity), Spoint))*0.6)'}>} total <Spoint> Date)

Took the date out of the pivot, as we are counting dates, you can put it back if you need it for some reason.

HTH

Jonathan

Re: Count dates with condition

Anonymous — Tue, 19 Aug 2014 06:51:48 GMT

your expression is not correct for my need.

I need to calculate count of only that dates,where quantity>0.6*avg(sum(total <Spoint> quantity )) something like count({<dates,where quantity>0.6*avg(sum(total <Spoint> quantity ))>} total <Spoint> Date)

Thank you!

Re: Count dates with condition

Anonymous — Tue, 19 Aug 2014 07:09:48 GMT

it's working...

but i mean 60 % not of total avg quantity of all Spoints, but of total quantity for each Spoint.

for example.. for Spoint1 60% of total quantity = 0.6 * 3=1.8

for Spoint2 60 % of total quantity = 0.6 * 12=7.2

......

Thank you!

Re: Count dates with condition

jagan — Tue, 19 Aug 2014 07:17:03 GMT

Hi,

Try like this

=0.6 *(sum( total <Spoint> Quantity)/count({<Quantity={'>0'}>} total <Spoint> Date))

Regards,

Jagan.

Re: Count dates with condition

Anonymous — Tue, 19 Aug 2014 07:30:04 GMT

Maybe you didn't understand my task....

I need something like

count({<quantity={'>$(=0.6*avg(sum(total <Spoint>quantity)))'}>} distinct Date)

Re: Count dates with condition

jagan — Tue, 19 Aug 2014 07:46:32 GMT

Hi,

Try this expression

=Sum(Aggr(If(Sum(Quantity) > 0.6 *(sum( total <Spoint> Quantity)/count({<Quantity={'>0'}>} total <Spoint> Date)), 1, 0), Date))

Regards,

Jagan.

Re: Count dates with condition

simenkg — Tue, 19 Aug 2014 08:02:10 GMT

Try something like:

if(Quantity > Fractile(Total Quantity,0.6),count(Date))

or maybe

count(if(Quantity > Fractile(Total Quantity,0.6),Date))

Hope this helps

Regards

SKG