My bar chart has 2 dimensions:
1.periods - cyclic selection
2. metric code (1-4)
1.bar - order quantity
2.text - average duration of all segments for the current period (e.g., date)
I need to display an average for each column, according to a selected cyclic dimension.
But the following expression doesn't work, as all the values are the same.
sum(TOTAL <$(='[' & GetCurrentField([Cyc_SaleOrd_Periods]) & ']')> Order_OpenTarget_TimeDiff)
/count(TOTAL <$(='[' & GetCurrentField([Cyc_SaleOrd_Periods]) & ']')> DISTINCT SaleOrder_ID)
Would appreciate another way to display it...
Message was edited by: Dafnis X Attached sample data
Solved! Go to Solution.
It's a great experience to solve your problem. Thank you 😃
There is one more solution which can be the one you lookin' for:
=sum(total <$(=GetCurrentField(Periods))> Duration)/sum(total <$(=GetCurrentField(Periods))> [Qty Orders])
We've finnaly reached the GetCurrentField() function =D
Perhaps, you are getting one value for all columns due to exact a total prefix. Try to use an aggr(sum(),dates)/aggr(count(),dates). I don't sure for real, but in some case it may help.
I still don't get why you want to use a total prefix if you want an average for each column in your chart. If I understood you right it should be enough to use expression w/o a total prefix. PFA.
Thanks so much for your kind help!
I probably was wrong with what how I was trying to do it..
For Each bar the value should display the average duration of an order according to the dimension selected.
If that's what your calculation tries to achieve, that i think it's wrong.
sum(duration)/sum(orders) = 1.5 (~30/20)
but you got 2.4
Can you please explain?
My apologize, I didn't check result well. 2.4 - is the average of one metric. In case of calculating all metrics using of Total prefix seems to be the way.
So, the solution will be that:
=sum(total <OpenWeek,OpenDate> Duration)/sum(total <OpenWeek,OpenDate> [Qty Orders])