Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Display Previous Month Values in Pivot Table

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:

error loading image

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?

1 Solution

Accepted Solutions
jsn
Honored Contributor

Display Previous Month Values in Pivot Table

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.

5 Replies
jsn
Honored Contributor

Display Previous Month Values in Pivot Table

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

Not applicable

Display Previous Month Values in Pivot Table

jsn:

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

Along these lines? (not working)

jsn
Honored Contributor

Display Previous Month Values in Pivot Table

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.

Not applicable

Display Previous Month Values in Pivot Table

Thanks for your fantastic reply jsn!

Not applicable

Re: Display Previous Month Values in Pivot Table

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

Community Browser