topic Re: Camparing Current Month(Max Date) and Past 2 Months in QlikView

Camparing Current Month(Max Date) and Past 2 Months

mohdhaniff — Wed, 25 Nov 2020 16:16:04 GMT

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'.

Re: Camparing Current Month(Max Date) and Past 2 Months

Gysbert_Wassenaar — Mon, 19 Dec 2016 08:46:37 GMT

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])

Re: Camparing Current Month(Max Date) and Past 2 Months

mohdhaniff — Mon, 19 Dec 2016 09:52:29 GMT

Thanks Gysbert,

I already try solution given but results = 0

Re: Camparing Current Month(Max Date) and Past 2 Months

Gysbert_Wassenaar — Mon, 19 Dec 2016 10:11:51 GMT

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])

Re: Camparing Current Month(Max Date) and Past 2 Months

mohdhaniff — Tue, 20 Dec 2016 01:25:16 GMT

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.

Re: Camparing Current Month(Max Date) and Past 2 Months

Gysbert_Wassenaar — Tue, 20 Dec 2016 06:47:40 GMT

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

Re: Camparing Current Month(Max Date) and Past 2 Months

mohdhaniff — Tue, 20 Dec 2016 07:17:25 GMT

Thanks..