Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
qlikview979
Valued Contributor

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
Partner
Partner

Re: Previous MONTH ID should be zero

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

4 Replies
Partner
Partner

Re: Previous MONTH ID should be zero

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
Valued Contributor

Re: Previous MONTH ID should be zero

Hi Aliolos Zhao,

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

Regards
Mahesh
Partner
Partner

Re: Previous MONTH ID should be zero

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

Highlighted
qlikview979
Valued Contributor

Re: Previous MONTH ID should be zero

Thanks Aliolos Zhao..

Its working as expected