Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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'.
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])
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])
Thanks Gysbert,
I already try solution given but results = 0
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])
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.
Yes, you're nesting single quotes within single quotes. Replace the outer single quotes with double quotes and the editor will be happy.
Thanks..