topic Re: Previous MONTH ID should be zero in QlikView

Previous MONTH ID should be zero

qlikview979 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

Please find attached Qlik app

I have data like below

ID	PRODUCT	SALES	MONTH	YEAR
1	A	100	August	2018
2	B	200	September	2018
3	C	300	October	2018
4	D	400	November	2018
5	E	500	December	2018

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

Re: Previous MONTH ID should be zero

uacg0009 — Mon, 01 Apr 2019 04:05:28 GMT

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

Re: Previous MONTH ID should be zero

qlikview979 — Mon, 01 Apr 2019 04:14:41 GMT

Hi Aliolos Zhao,

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

Regards
Mahesh

Re: Previous MONTH ID should be zero

uacg0009 — Mon, 01 Apr 2019 04:23:11 GMT

Hi Mahesh,

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

Aiolos Zhao

Re: Previous MONTH ID should be zero

qlikview979 — Mon, 01 Apr 2019 05:33:44 GMT

Thanks Aliolos Zhao..

Its working as expected