topic Re: Calculating M-1 and 12 rolling months in App Development

Calculating M-1 and 12 rolling months

Aspiring_Developer — Thu, 09 Jul 2020 10:37:53 GMT

hello All,

I need to calculate M-1 and rolling 12 months .

when user select Jun-20 from filter, M-1 should display the sum of previous months and 12RM should display the sum of all the months of previous year.

Here the filter used is NewTestVb1

I have below dates

I am using below expression :-

=sum({<Indicateur.Code = {'PO.1.701'}, NewTestvb1= {'=$(=((max(NewTestvb1)-1)))'}>} Indicateur.Value)

I think i should use Date.Num Month , i tried to get the max(date.NumMonth)-1 but it is not giving me the proper date.

I tried this =addmonths(max(Date.NumMonth),-1) but it is giving date format like dec-1899.

Can anyone please help ?

Thank You

Re: Calculating M-1 and 12 rolling months

arulsettu — Thu, 09 Jul 2020 11:09:37 GMT

Addmonths function requires date field like below. which will give you same date last month.

Addmonths(Date(Today()),-1)

examples

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/DateAndTimeFunctions/addmonths.htm

share some sample file

Re: Calculating M-1 and 12 rolling months

Aspiring_Developer — Thu, 09 Jul 2020 11:53:31 GMT

@arulsettu

Hi,

I tried using add months

Addmonths(max(Date),-1) and it gives output 01/05/2020- which is correct as my max month is june.

But when i am passing the same in the expression and select any month from the filter, it is returning me the current month value instead of previous month.

Please see the expression:-

=sum({<Indicateur.Code = {'PO.1.701'},Date.Year=,[Date.MonthEnd]=, NewTestvb1,[Date.MonthName]=, Date={'=$(=(addmonths(max(Date),-1)))'}>} Indicateur.Value)

It is giving me the output but when i select june2020, it should give me the value of May2020 but it is giving me the value of June only.

Please suggest.

Re: Calculating M-1 and 12 rolling months

arulsettu — Thu, 09 Jul 2020 11:58:42 GMT

Can you share sample app

Re: Calculating M-1 and 12 rolling months

Aspiring_Developer — Fri, 10 Jul 2020 05:23:23 GMT

Data Set

LOAD SITE.REFERENCE,
Cle_Fichier,
Fichier,
%Key_Date,
Indicateur.Code,
Indicateur.Name,
Indicateur.Value,
Indicateur.ValueConso,
BU.REFERENCE,
DIVISION.REFERENCE,
GROUPE.REFERENCE,
MoisReference
FROM
[Y:\POG_Sources_Documents\HR Divisions Scorecard\Data\Base_QVD\Fact_NewQS.qvd]
(qvd);

LOAD %Key_Date,
Date,
Date(Date,'MMM-YY') as NewTestvb1,
Date(Date, 'DD/MM/YYYY') as Addmonths_Date,
num(Date) as new,
New_Date,
Date.Month,
Date.NumMonth,
Date.MonthName,
Date.PreviousYearMonthName,
Date.PreviousMonthName,
Date.NextMonthName,
[Date.NextMonthName+1],
Date.Year,
Date.QuarterName,
Date.Quarter,
Date.MonthEnd,
Date.NextMonthEnd,
[Date.NextMonthEnd+1],
Date.PreviousMonthEnd,
Date.PreviousYearMonthEnd,
Date.YearStart,
Date.PreviousYearStart
FROM
[Y:\POG_Sources_Documents\HR Divisions Scorecard\Data\Base_QVD\Time_QS.qvd]
(qvd)
where day(Date)=1;

i have converted the date into num and passed the below expression:-

=Sum({<Indicateur.Code = {'PO.1.701'},new={'=$(=num(addmonths(max(new),-1)))'}>} Indicateur.Value)

This should return me the value only for May2020 but it giving me the overall value.

Please help