topic Re: Rolling 3,6,12 months Calculation in QlikView

Rolling 3,6,12 months Calculation

Fri, 19 Jun 2015 11:07:16 GMT

Hi all,

I need help for some calculations.

In my Dashboard I filtered one year of data, and I need to calculate the sum value stock of warehouse that moved > 3 Months, ❤️ Months, > 6 Months and > 12 Months.

How can i do?

Thank you so much.

Francesca

Re: Rolling 3,6,12 months Calculation

sunny_talwar — Fri, 19 Jun 2015 11:12:09 GMT

May be this link can help you: YTQ, QTD, MTD and WTD

Best,

Sunny

Re: Rolling 3,6,12 months Calculation

abhaysingh — Fri, 19 Jun 2015 11:14:57 GMT

Hi Francesca,

What is the logic to calculate >3Month, <3Month etc??

try this may help you, will give you last three month stock

=sum({< month = { '$((today()-3))'}>}Stockvalue)

thanks

Re: Rolling 3,6,12 months Calculation

sunny_talwar — Fri, 19 Jun 2015 11:17:41 GMT

=sum({< month = { '$((today()-3))'}>}Stockvalue)

Abhay, I don't think the above will give him last 3 months stock. Today() - 3 is three days back. Did you forget to add Addmonths??

Re: Rolling 3,6,12 months Calculation

abhaysingh — Fri, 19 Jun 2015 11:52:27 GMT

yeah sory pls add addmonths

=sum({< month = {'$(Addmonths(Today()),-3)'}>}Stockvalue)

Re: Rolling 3,6,12 months Calculation

sunny_talwar — Fri, 19 Jun 2015 11:56:05 GMT

I would further adjust it to this:

=Sum({<MonthYear = {"$(='>=' & MonthName(AddMonths(Today(),-3)))"}>} Stockvalue)

Where MonthYear is a field created in the script using MonthName() function

[Master Calendar]:

LOAD Date,

Year,

MonthName(Date) as MonthYear

...

Resident...;

Re: Rolling 3,6,12 months Calculation

anu — Tue, 15 Oct 2019 13:10:50 GMT

Hi,

Thanks for the post,

=Sum({<MonthYear = {"$(='>=' & MonthName(AddMonths(Today(),-3)))"}>} Amount)

I'm using above measure in a bar chart with dimension as MonthYear and measure column as Amount, but it is not working , showing me a blank chart.

Re: Rolling 3,6,12 months Calculation

martinpohl — Tue, 15 Oct 2019 13:20:49 GMT

I think,

Set analysis won't work with monthname and <= or >=

use

=Sum({<Datefield= {"$(='>=' & date(AddMonths(Today(),-3)))"}>} Amount)

instead