QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for
Did you mean:
Not applicable

How to use different dimension for chart and expression?

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.

T.

1 Solution

Accepted Solutions
MVP

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

MVP

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