topic Comparing 2 different date fields against each other with IF statement to create new dimension in App Development

Comparing 2 different date fields against each other with IF statement to create new dimension

JustinM — Wed, 01 May 2024 17:27:15 GMT

I'm trying to create a dimension where I can compare two different date fields to each other and return a result from an IF statement for each month in a pivot table format.

For example:

First Date field = Transaction Month [Transmonth]

Second Date field = Report Month [Reportmnth]

So I'm trying to show a pivot table with Report Month as column headings and then create a formula that compares the Transaction month to the Report month and if it is the same month then return "Current Month" , if the transaction period is the month before the Report Month return "Prior Month" and if the Transaction Period is older than 2 months return "Older".

Data would like this for example

Report Month	Transaction Month	Sales
Jan-24	Jan-24	20
Jan-24	Jan-24	25
Jan-24	Dec-24	30
Jan-24	Nov-24	50
Feb-24	Feb-24	10
Feb-24	Jan-24	15
Feb-24	Dec-24	20
Feb-24	Nov-24	25
Mar-24	Mar-24	20
Mar-24	Feb-24	30
Mar-24	Jan-24	40

And I'm trying to get it to look like this;

Report Month is the column headings

	Jan-24	Feb-24	Mar-24
Current Month	20	10	20
Prior Month	25	15	30
Older	80	45	40

I tried the following formula and it works for to return current month, but then only returns "Older" for the rest and not the prior month for the data for the month before.

=if([Transmonth.autoCalendar.YearMonth]=[Reportmnth.autoCalendar.YearMonth],'Current Month',
if([Transmonth.autoCalendar.Date]>MonthsEnd([Reportmnth.autoCalendar.Date],-2),'Prior Month','Older'))

Any help is much appreciated

Re: Comparing 2 different date fields against each other with IF statement to create new dimension

F_B — Mon, 20 May 2024 09:48:52 GMT

Hi @JustinM ,

I'm not sure about what Transmonth.autoCalendar.YearMonth and Reportmnth.autoCalendar.YearMonth are in your data model, but you can try with something like this:

=if(Date(Date#(Transmonth, 'MMM-YY')) = Date(Date#(Reportmnth, 'MMM-YY')), 'Current Month',
if(Date(Date#(Transmonth, 'MMM-YY')) = AddMonths(Date#(Reportmnth, 'MMM-YY'), -1), 'Prior Month',
if(Date(Date#(Transmonth, 'MMM-YY')) <= AddMonths(Date#(Reportmnth, 'MMM-YY'), -2), 'Older')))

Re: Comparing 2 different date fields against each other with IF statement to create new dimension

Lech_Miszkiewicz — Mon, 20 May 2024 10:10:30 GMT

Hi @JustinM

dont use autocalendar and use proper calendar by writitng it in script - autocalendar is just creating mess as is rather poor practice for
1. you can use in load script Date(MonthStart(Reportmnth),'MMM-YYYY') as [Report Month]
another good practice is to do necessary steps in load script so for that you can just create field during reload such as:
1. If(MonthStart(Transmonth)=MonthStart(Reportmnth),'Current Month', If(MonthStart(Transmonth)=MonthStart(Reportmnth,-1),'Prior Month','Older')) as XYZ
Then you use XYZ and[Report Month] as diemensions in your pivot table.

Lastly - using data manager is not a good practice for data modeling. It is much better to use load script editor where you have full control over what is happening with the data. If you are not familiar with it it would be good to start with learning data scripting in Qlik as that makes the whole UI build so much easier and simpler.

cheers

Re: Comparing 2 different date fields against each other with IF statement to create new dimension

JustinM — Mon, 20 May 2024 11:47:33 GMT

Thank you for this. It helps a lot. Not yet quite giving me the result I'm looking for but has pointed me in right direction, so that I can fiddle with it and correct my scripts. Will post if I find my error.