last 6 months in bar graph

[nareshthavidishetty]

hi all

actually im showing a bar graph with dimension as month for 12 months

but i need to show only 6 months data from the current month as i tried like this

created calculated dimension =If(Month > Month(AddMonths(VarToday, -6)) and Month <= Month(VarToday), Month)

but it is not working as it is showing from Apr to sep even if i select november,december

i cannot do in script part as i need to do in dimension

thanks,

Naresh

[sunny_talwar]

what is varToday? Is it a variable? How do you define it?

Also, I would suggest using set analysis instead of calculated dimension to get this done.

[vinieme12]

create a YearMonth field in your data table to simplify the calculations

example: 201601,201602 and so on

and vStartYearMonth = NUM(year(Today())) & NUM(month(Today()),'00')

and vEndYearMonth = Date(Addmonths( Date($(vStartYearMonth),'YYYYMM'),-6),'YYYYMM')

Just ensure your Month variables are '00'  format meaning Jan is '01' and not '1'

You expression should then be

SUM ( {< YearMonth = {">=$(vStartYearMonth)<$(vEndYearMonth)"} >} Sales)

with YearMonth you will be compare months over different years example 201611 to 201710

[sunny_talwar]

vinieme12‌ I am slightly confused...

Your vStartYearMonth is a number (not a date), so that would make me think that YearMonth is a number. But vEndYearMonth is a date. I don't think this will work. I think it will need to be either or. Don't you think so?

[vinieme12]

When you format Date to YYYYMM you will have a number like 201701

[sunny_talwar]

Nope... try adding Num(Date(Today(), 'YYYYMM')).... this will not give you 201702... Date is just a formatting function... underlying value is still the same....

[vinieme12]

Well it work's, just a slight change with vEndYearMonth

and vStartYearMonth = NUM(year(Today())) & NUM(month(Today()),'00')

and vEndYearMonth = Date(Addmonths( Date#($(vStartYearMonth),'YYYYMM'),-6),'YYYYMM')

[sunny_talwar]

It works? Really? I doubt it would... do you have a sample where you can show me this is working?

[vinieme12]

We are not formatting the whole date, instead we are splitting the Year  & Month, that is to Text and the encapsulating as NUM(TEXT) , so that why

it's like

=Num('002')  = 2

[sunny_talwar]

I see what you mean.... but trust me... I still don't think the above will work.... have you tested it? If you have and it works... I might get to learn something new here