Dynamically Changing MONTH

[#1]

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‌

[Mahamed_Qlik]

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

[marcus_sommer]

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

[#4]

No its not working

[#5]

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

[#6]

Anyone Please help me on this

[marcus_sommer]

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