Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

andreyfcdk91
New Contributor III

Count dates with condition

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!

1 Solution

Accepted Solutions
MVP
MVP

Re: Count dates with condition

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
8 Replies
MVP
MVP

Re: Count dates with condition

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.

MVP
MVP

Re: Count dates with condition

Hi

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
andreyfcdk91
New Contributor III

Re: Count dates with condition

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!

andreyfcdk91
New Contributor III

Re: Count dates with condition

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!

MVP
MVP

Re: Count dates with condition

Hi,

Try like this

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

Regards,

Jagan.

andreyfcdk91
New Contributor III

Re: Count dates with condition

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

I need something like

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

MVP
MVP

Re: Count dates with condition

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.

bwisenosimenkg
Valued Contributor

Re: Count dates with condition

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

Community Browser