8 Replies Latest reply: Aug 19, 2014 4:04 AM by Simen Kind Gulbrandsen

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

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

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

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

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

• ###### Re: Count dates with condition

Hi,

Try like this

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

Regards,

Jagan.

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

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

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