topic Re: Bar chart in QlikView

Bar chart

bharatkishore — Mon, 27 Aug 2018 12:56:48 GMT

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

Re: Bar chart

sunny_talwar — Mon, 27 Aug 2018 13:00:22 GMT

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))

Re: Bar chart

bharatkishore — Mon, 27 Aug 2018 13:07:41 GMT

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

Re: Bar chart

sunny_talwar — Mon, 27 Aug 2018 13:14:02 GMT

Are you able to share a sample?

Re: Bar chart

bharatkishore — Mon, 27 Aug 2018 13:22:59 GMT

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

Re: Bar chart

johanlindell — Mon, 27 Aug 2018 13:36:15 GMT

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.

Re: Bar chart

sunny_talwar — Mon, 27 Aug 2018 13:39:09 GMT

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))))

Re: Bar chart

bharatkishore — Mon, 27 Aug 2018 13:43:05 GMT

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.

Re: Bar chart

sunny_talwar — Mon, 27 Aug 2018 13:45:20 GMT

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!

Re: Bar chart

bharatkishore — Mon, 27 Aug 2018 13:47:17 GMT

Thank you Sunny Bhai.