Qlik Community

Ask a Question

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 
Search instead 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.

Thanks in advance!

T.

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

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

View solution in original post

1 Reply
MK_QSL
MVP
MVP

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

View solution in original post