Camparing Current Month(Max Date) and Past 2 Months

[mohdhaniff]

Hi.

I want to compare ETP Amount collected for the maximum (current) month with Previous and 2 Month Before.

For current Month, I already got the expression as below:

          sum({$<[Date Disbursed - Month]={'$(=max([Date Disbursed - Month]))'}>} [ETP Amount])

How can I get the value for Previous Month and Past 2 Month in expression?

My [Date Disbursed - Month] formatted as 'DD-MMM-YYYY'.

[Gysbert_Wassenaar]

Previous month: sum({$<[Date Disbursed - Month]={'$(=AddMonths(max([Date Disbursed - Month]),-1))'}>} [ETP Amount])

2 Months before: sum({$<[Date Disbursed - Month]={'$(=AddMonths(max([Date Disbursed - Month]),-2))'}>} [ETP Amount])

[mohdhaniff]

Thanks Gysbert,

I already try solution given but  results = 0

[Gysbert_Wassenaar · Accepted solution]

Perhaps the date format of that field differs from the document default. Try adding a date format:

sum({$<[Date Disbursed - Month]={'$(=Date(AddMonths(max([Date Disbursed - Month]),-1),'DD-MMM-YYYY'))'}>} [ETP Amount])

[mohdhaniff]

Thanks gysbert.

Earlier I thought something wrong with the formula due to red line. Today I learn something new about QlikView trick.

*P/S, If you don't mind, can you help me on another issue related to this solution?

Comparing Total Collected between 2 month within the same period

For this query, I wish to do set analysis to compare amount collected for current month and previous month (or previous 2 month). The different is I want to know how much amount collected on previous month within the same period collected on current month.

e.g.: Current month number of days is 4, then how much collected for the same period on previous month.

[Gysbert_Wassenaar]

Yes, you're nesting single quotes within single quotes. Replace the outer single quotes with double quotes and the editor will be happy.

[mohdhaniff]

Thanks..