Displaying the current and previous month value in bar chart with two dimensions.

[#1]

Hi,

I requirement is to create a barchart with State as its dimension and Sum(No.of.Subscribers) as its expression, where the Sum(No.of.Subscribers) of current and previous months only should be displayed. Please refer to the image below for reference if you dont understand my requirement. I have attached my data. Can someone help!

[PrashantSangle]

Hi,

create 2 expression

1 for current month and 2 for previous month

1: current Month

sum({<Year={"$(=max(Year))"},Month={"$(=max(Month))"}>}[No of Subscribers])

2:

sum({<Year={"$(=max(Year))"},Month={"$(=max(Month)-1)"}>}[No of Subscribers])

check month format might required maxstring instead of max use as per requirement.

Search on community for CYMTD and LYMTD expression

Regards

[#3]

I tried whatever you said, but its not working can you please share me a .qvw file?

[sunny_talwar]

I think the problem is that your Month field is not read as date and hence you are able to use Max(Month) in set analysis. In your script, try this:

Month(Date#(Month, 'MMM')) as Month,

Num(Month(Date#(Month, 'MMM'))) as MonthNum,

now try using MonthNum in your expression

sum({<Year={"$(=max(Year))"},MonthNum = {"$(=max(MonthNum )"}>}[No.of.Subscribers])

Having said that, I would be concerned about this expression when it's Jan of the year. In that case, I would suggest you to create a month year field and use that in place of Month and Year separately

Date(MakeDate(Year, Month(Date#(Month, 'MMM'))), 'MMM-YYYY') as MonthYear

and then this:

This month:

Sum({<MonthYear={"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}>}[No.of.Subscribers])

Last month:

Sum({<MonthYear={"$(=Date(AddMonths(Max(MonthYear), -1), 'MMM-YYYY'))"}>}[No.of.Subscribers])

[#5]

I tried using your steps and got the result. But when I click some other month like april, only the values of April is shown. Please check the attached file.

[sunny_talwar · Accepted solution]

Ignore selection in Month field

Sum({<MonthYear={"$(=Date(Max(MonthYear), 'MMM-YYYY'))"}, Month>}[No.of.Subscribers])

Sum({<MonthYear={"$(=Date(AddMonths(Max(MonthYear), -1), 'MMM-YYYY'))"}, Month>}[No.of.Subscribers])

[#7]

Thank you very much.