# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

## 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

## 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

## 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
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!

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

## Re: Count dates with condition

Hi,

Try like this

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

Regards,

Jagan.

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

## 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.

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