Compare This year and last Year Products by flag

[New-Qlik]

HI,

I have to create comparable flag, so if an item present in previous year flag should say 'Yes' and if an item is not present in Previous year(that means new item) than flag should say 'No'

Product_table :

Load

Product_id,

Amount,

Fiscal_year

from Product 

Left join 

Load 

Product_id,

Fiscal_year -1,

if(Sum(amount)> 0 , 'Yes', 'No') as Comparable_Flag

Resident Product

Group By 

Product_id,

Fiscal_year -1;

This flag works fine for Yes condition but for no all the items should come.

But instead of going to 'No' part of condition its goes to  '_'(Null).

How to make no working fine?

Regards

AT

[New-Qlik]

HI Kaanerisen,

I tried same but it gives me 'NO' as flag for all Products.

Product:
Load
*,
IF(isnull(Lookup('Product','ProductYear',ProductPrevYear)),'No', 'YES') as FLG;
load
Product&'-'&TEXT([Fiscal Year]) as ProductYear,
Product&'-'&TEXT(NUM([Fiscal Year])-1) as ProductPrevYear,

Product,

Amount,

[Fiscal Year]

from 

FROM [lib://...\QVD_Product.qvd](qvd)

[kaanerisen]

You should check if the calculated fields ProductYear and ProductPrevYear is null or not. Are they coming null after reload?

If you can share a sample of the dataset you use, I can look further.

[New-Qlik]

in ProductYear and ProductPrevYear do have value as calculated. its is not null. Data is huge so cannot share qvd file , I will create sample data and will share.