Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
jsn:
Sum({$<Month={$(=(AddMonths(max(GLTranDate,0),-1)))}>} GLTranAmount)
Along these lines? (not working)
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.
Thanks for your fantastic reply jsn!
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