Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, need to have a 3 month bar chart based on the below data and it should change automatically on change of month/year. please let me know if anyone can help me here..thnx
MonthEndDate | A | B | C |
1/31/2016 | 1 | 10.3 | 80 |
2/28/2016 | 2 | 15.6 | 80 |
3/31/2016 | 3 | 20.9 | 80 |
4/30/2016 | 4 | 26.2 | 80 |
5/31/2016 | 5 | 31.5 | 80 |
6/30/2016 | 6 | 36.8 | 80 |
7/31/2016 | 7 | 42.1 | 80 |
8/31/2016 | 8 | 47.4 | 80 |
9/30/2016 | 9 | 52.7 | 80 |
10/31/2016 | 10 | 58 | 80 |
11/30/2016 | 11 | 63.3 | 80 |
12/31/2016 | 12 | 68.6 | 80 |
1/31/2017 | 13 | 73.9 | 80 |
2/28/2017 | 14 | 79.2 | 80 |
3/31/2017 | 15 | 84.5 | 80 |
Which metric you want to use here. This case, You want after selection. May be this?
Sum({<MonthEndDate = {">= AddMonths(Max(MonthEndDate),-3) <= Max(MonthEndDate)"}>}A+B+C)
Or
with date format
Sum({<MonthEndDate = {">= Date(AddMonths(Max(MonthEndDate),-3),'MM/DD/YYYY') <= Date(Max(MonthEndDate),'MM/DD/YYYY')"}>}A+B+C)
Thnx Anil. For some reason i am getting 0 as a result and chart is not appearing..Any idea why?
Can you select 3/31/2017? Then look the data
My Fault,
Sum({<MonthEndDate = {">= $(=Date(AddMonths(Max(MonthEndDate),-3),'MM/DD/YYYY')) <= $(=Date(Max(MonthEndDate),'MM/DD/YYYY'))"}>}A+B+C)
Its still 0. thnx
sry, no luck..showing 0..
Would you share sample qvw
Sorry, due to privacy I cant..but below one worked Also tested by selecting the dates and it works fine.. Now how can I have a MM-YYYY format and select instead of date... thnx for your help
=sum({<MonthEndDate={'>=$(=MonthStart(Max(MonthEndDate),-3))<=$(=MonthStart(Max(MonthEndDate),1))'}>} A+B+C)
May be MonthName(MonthEndDate,'MM-YYYY') or MonthName(MonthEndDate)
Your expression seems you want to show Measure for last three months data with starting date of Month and till This month(I meant, Selection) of monthstart date. That case above condition should work. From here you can make in set analysis
=sum({<MonthEndDate={'>=$(=Date(MonthStart(Max(MonthEndDate),-3)),'YourDateFormat')<=$(=Date(MonthStart(Max(MonthEndDate),1)),'YourDateFormat')'}>} A+B+C)