topic Re: Load formula for changing months and years in QlikView

Load formula for changing months and years

Strooprover — Wed, 24 Feb 2021 08:43:35 GMT

Hi,

I have a table with the following structure:

Article,
DemandYear,
DemandMonth,
Demand1

I want to link this table to a different table based on the article field.
In the end I only want to sum up the 'Demand1' values for the 3 most recent monts in my QV report.

Is it possible to write a variable formula in the load script (or later in the QV file) to get this summed up value no matter which month the current month/year is?

Much thanks in advance.

Re: Load formula for changing months and years

MayilVahanan — Wed, 24 Feb 2021 09:48:33 GMT

Hi @Strooprover

Try like this

In Script,

MakeDate(DemandYear, DemandMonth,1) as DemandDate

In front end, try like this

Sum({<DemandDate = {">=$(=MonthStart(Max(DemandDate),-2)<=$(=MonthEnd(Max(DemandDate))"}>}Demand1)

Might be, it helps

Re: Load formula for changing months and years

Strooprover — Wed, 24 Feb 2021 10:55:27 GMT

Hi @MayilVahanan ,

Thank you for your quick reply!

I've made the adjustments in the script.
The 'DemandDate' field is correctly made, but I seem to have problems with the frond end part.

When I make a regular sum of 'Demand1' I get a value, but with your sum I get a empty value.
In added a testfile as example.

Could you perhaps look why I don't get a correct sum?

Much thanks in advance!

Re: Load formula for changing months and years

MayilVahanan — Wed, 24 Feb 2021 11:24:24 GMT

Hi @Strooprover

Try like below

Sum({<DemandDate={">=$(=MonthStart(Max(DemandDate),-2))<=$(=MonthEnd(Max(DemandDate)))"}>}Demand1)