topic Re: Dynamically Changing MONTH in QlikView

Dynamically Changing MONTH

Fri, 20 Nov 2020 18:02:30 GMT

Hi All,

The below is the my Data base table:

YEAR	QUARTER	MONTH

2010	Q4	12
2011	Q4	12
2012	Q4	12
2013	Q1	1
2013	Q2	5
2013	Q4	12
2014	Q2	5
2014	Q2	6
2014	Q4	12
2015	Q2	6
2015	Q4	12

In the above table we have YEAR, MONTH and QUARTER

Now i want to display my Dimension in my BAR CHART with Dynamically Changing the YEAR like this

Presently i have done hard code in my dimension like this "if(MONTH='Jun', 'YTD'& ' ' &MONTH& ' ' &YEAR,if(MONTH='Jan' or MONTH='Dec',YEAR))"

This Dimension should Change dynamically

1) If it is latest year it has to be display as 2015 year for december month and "YTD Jun 2015" YEAR(For this it has to pick which is latest month from JAN to NOV only)

2) For Previous year it has to be display as 2014 year for december month and "YTD Jun 2014" YEAR(For this it has to pick which is latest month from JAN to NOV only)

3) For the all previous to previous YEARS it has to be display as 2013,2012,2011 YEAR(It has to be taken ONLY DECEMBER Month)

Thanks

Avinash

jagan‌ TamilArasu‌ New to QlikView‌

Re: Dynamically Changing MONTH

Mahamed_Qlik — Fri, 29 Jan 2016 07:49:55 GMT

Hi Avinash,

You can create one variable for month lets say vMonth

Then instead of using the Month field, use this variable in your expression :

"if(vMonth='Jun', 'YTD'& ' ' &vMonth& ' ' &YEAR,if(vMonth='Jan' or vMonth='Dec',YEAR))"

Hope it helps...!

Regards,

Mahamed

Re: Dynamically Changing MONTH

marcus_sommer — Fri, 29 Jan 2016 08:01:36 GMT

If you used this quite often respectively in many objects you should consider to create such dimensions within the script, maybe with an The As-Of Table. If not if might be easier to achieve your wanted sight if you removed this dimension and used for each period an own expression like:

sum({< Year = {"$(=max(Year)-6)"}>} YourValue)

sum({< Year = {"$(=max(Year)-5)"}>} YourValue)

sum({< Year = {"$(=max(Year)-4)"}>} YourValue)

sum({< Year = {"$(=max(Year)-3)"}>} YourValue)

sum({< Year = {"$(=max(Year)-2)"}>} YourValue)

...

sum({< Year = {"$(=max(Year)-2)"}, Month = {"<=6"}>} YourValue)

...

- Marcus

Re: Dynamically Changing MONTH

Fri, 29 Jan 2016 08:24:37 GMT

No its not working

Re: Dynamically Changing MONTH

Fri, 29 Jan 2016 08:26:04 GMT

Can you please suggest me for my Hard coded dimesnion only. I want to do in dimension only.

Re: Dynamically Changing MONTH

Fri, 29 Jan 2016 12:33:20 GMT

Anyone Please help me on this

Re: Dynamically Changing MONTH

marcus_sommer — Fri, 29 Jan 2016 12:49:27 GMT

It's not possible as hardcoded dimension then 2014/2015 and the YTD Jun 2014/2015 are overlapping each other. As alternatively to my above suggested approaches you could use a synthetic dimension with valuelist() - but you will need use these valuelist within the expression, too with an if-loop and all single-expression like above mentioned - which made it not necessary easier.

- Marcus