Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

];

Community Browser