How to use different dimension for chart and expression?

[#1]

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.

[MK_QSL · Accepted solution]

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