Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikview979
Specialist
Specialist

Previous MONTH ID should be zero

Hi All,

Please find attached Qlik app

I have data like below

IDPRODUCTSALESMONTHYEAR
1A100August2018
2B200September2018
3C300October2018
4D400November2018
5E500December2018

 

LOAD ID,
PRODUCT,
SALES,
MONTH,
if(Left(MONTH,3)='Aug',8,if(Left(MONTH,3)='Sep',9,if(Left(MONTH,3)='Oct',10,if(Left(MONTH,3)='Nov',11,if(Left(MONTH,3)='Dec',12))))) as MONTH_ID,
YEAR
FROM
test.xlsx]
 (ooxml, embedded labels, table is Test);

I have created two variables for

vMaxmonthid=max(MONTH_ID)

vMinmonthid=(max(MONTH_ID)-1)

and if i select August month as  its showing maxmonthid is 8 and minmonthid is showing 7.

Here i don't have July month so min variable should be show Zero in variable level.It means if i don't have previous month data in it should show zero.

How to handle this scenario in qlikview can you help me on this?

 

Regards

Mahesh

 

1 Solution

Accepted Solutions
uacg0009
Partner - Specialist
Partner - Specialist

Hi Mahesh,

Please see below screenshots and attachment, I think that's what you want. (v1 and v2)

Previous MONTH ID should be zero 1.pngPrevious MONTH ID should be zero 2.png

Aiolos Zhao

View solution in original post

4 Replies
uacg0009
Partner - Specialist
Partner - Specialist

Hi,

Please try below code, it works in my sample:

set vMaxmonthid = max(MONTH_ID);
set vMinmonthid = if(max(MONTH_ID) - 1 < min({<MONTH_ID=>}MONTH_ID),0,max(MONTH_ID) - 1);

And in the front-end, you can use $() to get the variable value.

Please try.

Aiolos Zhao

qlikview979
Specialist
Specialist
Author

Hi Aliolos Zhao,

I tried its not working.can you try with my sample attached file?

Regards
Mahesh
uacg0009
Partner - Specialist
Partner - Specialist

Hi Mahesh,

Please see below screenshots and attachment, I think that's what you want. (v1 and v2)

Previous MONTH ID should be zero 1.pngPrevious MONTH ID should be zero 2.png

Aiolos Zhao

qlikview979
Specialist
Specialist
Author

Thanks Aliolos Zhao..

Its working as expected