Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
No its not working
Can you please suggest me for my Hard coded dimesnion only. I want to do in dimension only.
Anyone Please help me on this
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