Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I ask a question about creating a little bit complicated expression (at least for me!).
I would like to calculate an expression using WEEK as dimension, but display values using DAY as dimension.
I try to explain better.
I have data of order and their delivery date and I would like to calculate the average order value for delivery week and display it on a chart with delivery date dimension.
More or less, I have...
Delivery_Date, Order_Value
25/08/2014, 40
04/09/2014, 100
04/09/2014, 20
04/09/2014, 80
05/09/2014, 30
05/09/2014, 30
06/09/2014, 30
12/09/2014,110
...
Only the delivery date must be considered as working day.
For example, 25/08/2014 IS a working day but 26/08 IS NOT a working day.
First, I would like to calculate the average order value per week considering only the working days (i.e. the "delivery days").
For instance, the week 36 (from 01/09 to 07/09) has 3 working days.
So, the average order value of the week is (100 + 20 + 80 + 30 + 30 + 30) / 3 = 96,7
Then, I have to display this value on a chart for each delivery date.
Delivery_Date, Average order value per week
25/08/2014, 40
04/09/2014, 96,7
05/09/2014, 96,7
06/09/2014, 96,7
12/09/2014,110
Hope to be clear enough.
Thanks in advance!
T.
Add one more line in your script...
Week(Delivery_Date) as DeliveryWeek
Now Create a Straight Table
Dimension
Delivery_Date
DeliveryWeek
Expression
SUM(TOTAL <DeliveryWeek> {<Delivery_Date = >}Order_Value)/Count({<Delivery_Date = >}TOTAL <DeliveryWeek >DISTINCT Delivery_Date)
Go to presentation tab
Select DeliveryWeek >> Hide Column
Hope this will help
Add one more line in your script...
Week(Delivery_Date) as DeliveryWeek
Now Create a Straight Table
Dimension
Delivery_Date
DeliveryWeek
Expression
SUM(TOTAL <DeliveryWeek> {<Delivery_Date = >}Order_Value)/Count({<Delivery_Date = >}TOTAL <DeliveryWeek >DISTINCT Delivery_Date)
Go to presentation tab
Select DeliveryWeek >> Hide Column
Hope this will help