topic Re: Last 3 months calculation in QlikView

Last 3 months calculation

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

Hi All,

In the below Qvw there is AMS (Average monthly Stock ) --

Distributor stock Value/ last 3 months of Indirect sales Value

Example : if we are in Jun-2015 the value should be 273,589/1,026422+468,380+503,819/3

the above condition is satisfied but when we are selecting Mar-2016 year and month filter ,

the APR-2015, MAY-2015 is not capturing last 3 months data (it is calculating only previous month data)

can we do capture back end data in the table?

can any help on this??

Regards,

Devendar

Re: Last 3 months calculation

sunny_talwar — Tue, 31 May 2016 14:10:19 GMT

Not sure I understand what is not getting captured for Mar-2016?

Re: Last 3 months calculation

Anonymous — Tue, 31 May 2016 14:27:35 GMT

check the Apr-2015 AMS value , it is not calculating last 3 months value

Re: Last 3 months calculation

sunny_talwar — Tue, 31 May 2016 14:32:39 GMT

For Apt-2015 it is doing this:

(1,030,188)/((1,026,422+522,534+503,819)/3) = 1.5055 or 1.51. What is the issue?

Re: Last 3 months calculation

Anonymous — Tue, 31 May 2016 14:37:52 GMT

if you click on MAR-2016 then APR-2015 value is changing, it is supposed to be not change right??

Re: Last 3 months calculation

Anonymous — Tue, 31 May 2016 14:40:54 GMT

Re: Last 3 months calculation

sunny_talwar — Tue, 31 May 2016 14:45:01 GMT

Check now

Expression:

=Sum({$<Month=, Year=,[Indirect Sales Type ID]={'Stock'} ,Stock_Status={'Available'}, CALEN_DT={">=$(=MonthStart(AddMonths(Max(CALEN_DT),-11)))<$(=MonthEnd(Max(CALEN_DT)))"}>} $(Vunit))/

rangeAvg(Before(Sum({$<Month=, Year=,[Indirect Sales Type ID]={'GRSLS@GSK','GOODRET@GSK'},

CALEN_DT=>}$(Vunit)), 0, 3))

Re: Last 3 months calculation

Anonymous — Tue, 12 Jul 2016 14:55:20 GMT

Hi ,

when am using month_year as dimension in the basic view backward stock coverage volume showing correct but when am using the month name as a dimension in the above chart of basic view Backward stock coverage volume showing different value,

can you please help me out on this

Regards,

Devendar

Re: Last 3 months calculation

Anonymous — Tue, 12 Jul 2016 14:57:45 GMT

Last 3 months calaculation

you can see the application here

Re: Last 3 months calculation

sunny_talwar — Tue, 12 Jul 2016 15:00:56 GMT

Do you have the numbers you would want to see?

Re: Last 3 months calculation

Anonymous — Tue, 12 Jul 2016 15:05:10 GMT

yes, that last column backward stock coverage volume ,just look at the 2 charts, the value is not matching

the same expression applied in both charts but dimension is different

Re: Last 3 months calculation

Anonymous — Wed, 13 Jul 2016 04:45:48 GMT

Any luck??