Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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.
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
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!
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!
Hi,
Try like this
=0.6 *(sum( total <Spoint> Quantity)/count({<Quantity={'>0'}>} total <Spoint> Date))
Regards,
Jagan.
Maybe you didn't understand my task....
I need something like
count({<quantity={'>$(=0.6*avg(sum(total <Spoint>quantity)))'}>} distinct Date)
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.
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