Count dates with condition

[Anonymous]

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!

[jagan]

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.

[jonathandienst · Accepted solution]

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

[Anonymous]

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!

[Anonymous]

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!

[jagan]

Hi,

Try like this

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

Regards,

Jagan.

[Anonymous]

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

I need something like

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

[jagan]

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.

[simenkg]

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