topic Re: Rollig 12 Months in Bar chart in QlikView

Rollig 12 Months in Bar chart

qlik4asif — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have created a chart for Rolling 12 months on month and function as dimension below

Expression is

=Num(Sum({<Region={'Global'},DateMonth ={">$(=Date(AddMonths(vMaxDate_IIR,-12),'YYYY-MM')) <=$(vMaxDate_IIR)"}>} YTD), '##.##')

But it is showing Respective Month Value not the rolling 12 months for every line

Checked Dates and giving correct value ---- Date(AddMonths(vMaxDate_IIR,-12),'YYYY-MM') and to ---vMaxDate_IIR

When i tried expression with text box and giving selections accordingly it is giving rolling 12 months value

Please suggest me on this.

Thanks in advance

Re: Rollig 12 Months in Bar chart

Anil_Babu_Samineni — Thu, 23 Nov 2017 14:26:51 GMT

In your way, What do you mean the "Rolling 12 Months"??

Re: Rollig 12 Months in Bar chart

qlik4asif — Thu, 23 Nov 2017 14:34:42 GMT

Chart shows recent 12 months(Considering max date as 2017-10)

if you see in chart first date is

2016-11------value for this should be from 2015-12 to 2016-11

2017-10-----value for this should be from 2016-11 to 2017-10

Re: Rollig 12 Months in Bar chart

stabben23 — Thu, 23 Nov 2017 14:34:46 GMT

Hi,

{">$(=Date(AddMonths(vMaxDate_IIR,-12),'YYYY-MM'

if you add >= in the beginning of set analysis maybe.

{">=$(=Date(AddMonths(vMaxDate_IIR,-12),'YYYY-MM'

Re: Rollig 12 Months in Bar chart

Anil_Babu_Samineni — Thu, 23 Nov 2017 14:38:26 GMT

Then, This should work

=Num(Sum({<Region={'Global'},DateMonth ={">=$(=Date(AddMonths(vMaxDate_IIR,-12),'YYYY-MM')) <=$(vMaxDate_IIR)"}>} YTD), '##.##')

Re: Rollig 12 Months in Bar chart

qlik4asif — Thu, 23 Nov 2017 14:38:38 GMT

Why i have not included = because i need 12 months period, if i include = it will show 13 months data

Re: Rollig 12 Months in Bar chart

stabben23 — Thu, 23 Nov 2017 14:50:20 GMT

ok, if you add max here then?

{">$(=max(Date(AddMonths(vMaxDate_IIR,-12),'YYYY-MM')))

Re: Rollig 12 Months in Bar chart

stabben23 — Thu, 23 Nov 2017 15:13:22 GMT

Date(AddMonths(vMaxDate_IIR,-12),'YYYY-MM')) this will gives you 2016-10, but in Your case you have use just > in Your setanalysis, which is 2016-11. So Your Expression says, show bigger values than 2016-10 and that is 2016-11

Re: Rollig 12 Months in Bar chart

stabben23 — Thu, 23 Nov 2017 15:41:48 GMT

oh, I see your problem, you think that 2016-10 to 2017-10 is 12 month, its not, its 13.

You have to decide what is rolling 12, if you want to include both end period. 12 month back from 2017-10 is 2016-11 as you show in your pic, just count the month.

Re: Rollig 12 Months in Bar chart

qlik4asif — Fri, 24 Nov 2017 05:28:02 GMT

My max date is 2017-10

If i use addmonths(vMaxDate_IIR,-12) ...it gives 2016-10

NOw in expression i have given >addmonths(vMaxDate_IIR,-12) ....which gives 2016-11

and <=MaxDate means 2017-10

so from 2016-11 to 2017-10....which is 12 months period

The problem i am facing is graph is not giving 12 months rolling value, but it is showing according tomonth value

Re: Rollig 12 Months in Bar chart

stabben23 — Fri, 24 Nov 2017 07:00:28 GMT

I'm not sure what you try to achive, rolling 12 month is exactly what you are showing in Your graph.

Or is it some kind of dynamic graph that you want according to what selections you made?

Re: Rollig 12 Months in Bar chart

qlik4asif — Fri, 24 Nov 2017 09:10:27 GMT

The value showing are values on particular single month

But i need Rollong 12 months value per each month

For Eg:

If you see the orange line for 2017-10 the value it showing is 0.55

But i need to show the sum of the months which is 5.62(This is Rolling 12 months value)

same way i need to show for all months.

For 2016-11....the valuse should be Sum of 12 months from this month(2015-12 to 2016-11)

For 2016-12...................(2016-01 to 2016-12)

;

2017-10 ................(2016-11 to 2017-10)

Re: Rollig 12 Months in Bar chart

stabben23 — Fri, 24 Nov 2017 09:20:39 GMT

ok so you want a accumulated value for 12 month back in Your rolling 12 month graph.

That will twist Your Expression a lot I'll think.

Re: Rollig 12 Months in Bar chart

stabben23 — Fri, 24 Nov 2017 10:33:20 GMT

check if this Expression helps you, some small changes maybe needed.

Num(Sum({<Region={'Global'},DateMonth ={">$(=Date(AddMonths(vMaxDate_IIR,-12),'YYYY-MM')) <=$(vMaxDate_IIR)"}>}

aggr(RangeSum(above(sum(YTD),0,12)),DateMonth)), '##.##')

Re: Rollig 12 Months in Bar chart

qlik4asif — Mon, 27 Nov 2017 05:36:37 GMT

Tried with your expression, but giving only wrong values, that too only one line instead of 4.

But when i do selction for particular a function, it is giving correct value, but i clear it showing big values.

Re: Rollig 12 Months in Bar chart

stabben23 — Mon, 27 Nov 2017 06:31:55 GMT

If you have 4 Expressions, then you need to change all of them in the same way.

If you only have one Expression and two Dimension, then add the second Dimension in the aggr function like this.

Num(Sum({<Region={'Global'},DateMonth ={">$(=Date(AddMonths(vMaxDate_IIR,-12),'YYYY-MM')) <=$(vMaxDate_IIR)"}>}

aggr(RangeSum(above(sum(YTD),0,12)),DateMonth,dimension2)), '##.##')

Re: Rollig 12 Months in Bar chart

qlik4asif — Mon, 27 Nov 2017 07:11:17 GMT

Thanks Johansson

Solved but for getting correct values i have changed the expression to

=Num(Aggr(Rangesum(Above(Sum({<Region={'Global'}>}YTD),0,12)),Function,DateMonth),'##.##')