Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Wrong result with a condition in my expression

Hello,

I have a strange behavior on QlikView.

I have a pivot table where I have to use "if" condition to make the link between the dimension and the expression.

Let's say I have the following data:

Data:

LOAD year(Date) AS Year,

month(Date) AS Month,

*;

LOAD * INLINE [

Date, Group, Product, Sales

01/01/2012, Drink, Water, 1

02/01/2012, Drink, Soda, 2

03/01/2012, Food, Bread, 3

04/01/2012, Food, Meat, 4

05/02/2012, Drink, Water, 10

06/02/2012, Drink, Soda, 20

07/02/2012, Food, Bread, 30

08/02/2012, Food, Meat, 40

09/03/2012, Drink, Water, 100

10/03/2012, Drink, Soda, 200

11/03/2012, Food, Bread, 300

12/03/2012, Food, Meat, 400

13/04/2012, Drink, Water, 1000

14/04/2012, Drink, Soda, 2000

15/04/2012, Food, Bread, 3000

16/04/2012, Food, Meat, 4000

];

And an island with years and months:

DimYear:

LOAD * INLINE [

DimYear

2012

];

DimMonth:

LOAD month(makedate(2000, RowNo())) AS DimMonth

AUTOGENERATE 12;

I use as Dimension:

- Group

- Product

- "=date(makedate(DimYear, DimMonth), 'MMM YYYY')"

and as expression:

sum(if(DimYear = Year and DimMonth = Month, Sales))

This works like a charm:

2012-12-13_105018.png

Now, I have a constraint: I want the "Product" list box to be sorted with best products first, and other products after, based on the following table:

TableProduct:

LOAD * INLINE [

ProductName, TopProduct

Bread, 1

Water, 1

Soda, 0

Meat, 0

Other Product never sold, 0

];

(note that I have more products in this table that in the Sales table)

So, I've created a new table at the begining of my script:

ProductLoadOrder:

LOAD DISTINCT ProductName AS Product

RESIDENT TableProduct

ORDER BY TopProduct DESC, ProductName ASC

;

DROP TABLE TableProduct;

2012-12-13_105536.png

But once I've done that, my chart doesn't work anymore!

2012-12-13_105655.png

The month "jan 2012" works fine, but all the others months are ignored.

But this works if my field "Group" is collapsed:

2012-12-13_105914.png

Why?!?

I've found a workaround: load the Product first in the Data table, but I don't find this elegant.

Data:

LOAD DISTINCT ProductName AS Product

RESIDENT TableProduct

ORDER BY TopProduct DESC, ProductName ASC

;

DROP TABLE TableProduct;

CONCATENATE (Data)

LOAD year(Date) AS Year,

month(Date) AS Month,

*;

LOAD * INLINE [

Date, Group, Product, Sales

01/01/2012, Drink, Water, 1

02/01/2012, Drink, Soda, 2

03/01/2012, Food, Bread, 3

04/01/2012, Food, Meat, 4

05/02/2012, Drink, Water, 10

06/02/2012, Drink, Soda, 20

07/02/2012, Food, Bread, 30

08/02/2012, Food, Meat, 40

09/03/2012, Drink, Water, 100

10/03/2012, Drink, Soda, 200

11/03/2012, Food, Bread, 300

12/03/2012, Food, Meat, 400

13/04/2012, Drink, Water, 1000

14/04/2012, Drink, Soda, 2000

15/04/2012, Food, Bread, 3000

16/04/2012, Food, Meat, 4000

];

0 Replies