Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kvr9
Creator
Creator

Using a calculated variable as condition in Set Analysis

Hello Experts,

 

I got a requirement to provide alerts to user when a product is near to expire. my requirement is  i have send alerts to user 45days before my expire data i.e sending alerts that product will be expire soon

I have calculated like below

vDays = (date(date#(Expire_date, 'DD MMM YYYY'),'DD/MM/YYYY') - date(date#(Manf_date, 'DD MMM YYYY'),'DD/MM/YYYY'))

count({<$(vDays) ={'<= [45]'}>}Product)

The data is like below

Manf_date, Expire_date, Product

1/01/2021, 12/07/2021,A

02/11/2020, 31/12/2021,B

11/07/2021, 16/08/2021, C

 

But unable to get the output with above calculation , kindly let me know the how to resolve this issue.

 

Thank you inadvance.

Labels (4)
2 Solutions

Accepted Solutions
Saravanan_Desingh

Try this,

SET DateFormat='D/MM/YYYY';

tab1:
LOAD * INLINE [
Manf_date, Expire_date, Product
1/01/2021, 12/07/2021,A
02/11/2020, 31/12/2021,B
11/07/2021, 16/08/2021, C
];

SET vDays = Expire_date- Manf_date;

View solution in original post

Saravanan_Desingh

Exp: =count({<Product={"=$(vDays)<=45"}>}Product)

Output:

commQV90.PNG

View solution in original post

5 Replies
Saravanan_Desingh

Try like this,

vDays = (date(date#(Expire_date, 'DD MMM YYYY'),'DD/MM/YYYY') - date(date#(Manf_date, 'DD MMM YYYY'),'DD/MM/YYYY'))

count({<Product={"(date(date#(Expire_date, 'DD MMM YYYY'),'DD/MM/YYYY') - date(date#(Manf_date, 'DD MMM YYYY'),'DD/MM/YYYY'))<=[45]"}>}Product)
kvr9
Creator
Creator
Author

Thanks Saran for quick response.

 

is it not possible to use variable instead of complete calculation?

 

Also the solution is not working , getting 0 as answer

Saravanan_Desingh

Try this,

SET DateFormat='D/MM/YYYY';

tab1:
LOAD * INLINE [
Manf_date, Expire_date, Product
1/01/2021, 12/07/2021,A
02/11/2020, 31/12/2021,B
11/07/2021, 16/08/2021, C
];

SET vDays = Expire_date- Manf_date;

View solution in original post

Saravanan_Desingh

Exp: =count({<Product={"=$(vDays)<=45"}>}Product)

Output:

commQV90.PNG

View solution in original post

kvr9
Creator
Creator
Author

Thank you so much, it is working now.