topic Display Previous Month Values in Pivot Table in QlikView

Display Previous Month Values in Pivot Table

Fri, 24 Jul 2009 08:20:46 GMT

Hi,

I've searched the forums extensively for this topic which has been discussed at depth, and tried implementing the suggested formulas with little success.

Please see my pivot table below:

The user is required to select a Year and Month in list boxes.

The formula for This Month is:

Sum(GLTranAmount)

The formula for YTD (rolling financial year) is:

Sum({$<Month =>} If(InYearToDate(GLTranDate, vMaxDate, 0, 7), GLTranAmount))

The formula for Last Year Total (static) is:

Sum({$<[FinYear]={$(=max([FinYear])-1)},Month=>} GLTranAmount)

How can I best write the formula for Previous Month?

Display Previous Month Values in Pivot Table

Anonymous — Fri, 24 Jul 2009 08:27:54 GMT

Hi Matt,

I assume you have some kind of Date/Timestamp field from which you've extracted the Years and Months into their own fields.

If you want to look at the previous month the easiest approach is to have a full date or something like YYYYMM and then use the function AddMonths.

AddMonths(Timestamp,-1)

This subtracts one month and you don't run into problems when going from Jan to Dec for example.

//Jsn

Display Previous Month Values in Pivot Table

Fri, 24 Jul 2009 08:48:23 GMT

jsn:

Sum({$<Month={$(=(AddMonths(max(GLTranDate,0),-1)))}>} GLTranAmount)

Along these lines? (not working)

Display Previous Month Values in Pivot Table

Anonymous — Fri, 24 Jul 2009 17:09:25 GMT

Hi Matt,

First of all you can't just set the Month in the set analysis since if today is Jan and you want to look at the previous month you're also wrapping over to last year.

The quickest approach is likely to create a YearMonth field in the script. 200905, 200906 etc. and make sure it's formatted as a proper date.

Then you could apply:

Sum({$<YearMonth={$(=(AddMonths(YearMonth,-1)))}>} GLTranAmount)

This can of course be done on the fly in the expression and then applied on both Year and Month.. (beware of missing parentheses):

Sum({$<Month={$(=Month(AddMonths(date#(Year & Month,'YYYYMM'),-1)))}, Year={$(=Year(AddMonths(date#(Year & Month,'YYYYMM'),-1)))}>} GLTranAmount)

You might need a Num() around the month function too depending on if you use numeric or text format for display of your months.

I recommend going the script route as that makes it a whole let easier to maintain.

Display Previous Month Values in Pivot Table

Mon, 27 Jul 2009 15:52:10 GMT

Thanks for your fantastic reply jsn!

Re: Display Previous Month Values in Pivot Table

Fri, 27 Nov 2015 13:51:49 GMT

Hi Johannes

sorry if I'm jumping into this topic but the formula that you posted may fit my needs however as I'm Qlik rookie I need your help to edit.

Sum({$<Month={$(=Month(AddMonths(date#(Year & Month,'YYYYMM'),-1)))}, Year={$(=Year(AddMonths(date#(Year & Month,'YYYYMM'),-1)))}>} GLTranAmount)

My table has both fields (YEAR and MONTH) in # format, hence if i'm not mistaken some formula parts can be deleted but I'm not sure which

thanks in advance for your help

ciao

STEFANO