Bar chart

[bharatkishore]

Hi All,

I have  a bar chart. And i have taken two dimensions i.e. MonthYear and Cat.

Now when i take one dimension i.e. MonthYear and write the below expression. It is working fine.

=RangeSum(Above(SUM({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -36)))<=$(=Date(Max(MonthYear)))"}

>}(Consumption)),0, Month(Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -36)))<=$(=Date(Max(MonthYear)))"}>}Month)))). Below image for reference:

Now when i add one more dimension i.e. Cat the values are getting changed. Below image for reference:

Can you please help me what can i do, even though if  i add the dimension the values should not change.

Thanks,

Bharat

[sunny_talwar]

Try this

Only(({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -36)))<=$(=Date(Max(MonthYear)))"}>} Aggr(

RangeSum(Above(Sum({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -36)))<=$(=Date(Max(MonthYear)))"}

>} Consumption), 0, Month(Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -36)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, Cat, MonthYear))

[bharatkishore]

Sorry Sunny Bhai.. Not getting the right value. Below image for reference:

[sunny_talwar]

Are you able to share a sample?

[bharatkishore]

Please find the attach file Sunny Bhai.

If you filter for 2015 from July to 2015 Dec and cat as More than 7 year the correct value is 335 but with help of your expression it is 311.

Kindly check and let me know if you need anything more.

Thanks,

Bharat

[johanlindell]

Hi,

Are you trying to make an aggregate for each year? If so create a "Year month" column in your fact table and a new dimension table with a "Year month" and a "Year month YTD aggr" columns looking like this.

"Year month", "Year month YTD aggr"

2015-01, 2015-01

2015-01, 2015-02

2015-02, 2015-02

2015-01, 2015-03

2015-02, 2015-03

2015-03, 2015-03

2015-01, 2015-04

2015-02, 2015-04

2015-03, 2015-04

2015-04, 2015-04

etc.

[sunny_talwar · Accepted solution]

If you have QV12 or above... you can try this

Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -36)))<=$(=Date(Max(MonthYear)))"}>} Aggr(

RangeSum(Above(Sum({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -36)))<=$(=Date(Max(MonthYear)))"}

>} Consumption), 0, Month(Only({<MonthYear={">=$(=Date(AddMonths(Max(MonthYear), -36)))<=$(=Date(Max(MonthYear)))"}>} Month))))

, cat, (MonthYear, (NUMERIC))))

[bharatkishore]

Thank you so much Sunny Bhai...Thanks a lot..

One question why we have use numeric and what does it do? Is it because of version issue that we have to use numeric.

[sunny_talwar]

Monthyear field isn't sorted correctly in the scrip, so I am sorting it within the Aggr() function using NUMERIC sort. Read more here:

The sortable Aggr function is finally here!

[bharatkishore]

Thank you Sunny Bhai.