Compare Months based on selected months

[qlikwiz123]

I have 3 columns. Month (MMM-YY), Date (MM/DD/YYYY) and ID

I need to show Count(ID) in a text box based on three conditions:

By default, it should show Current Month MTD vs Previous Month MTD (If current month data is only until 15th of the month, it should compare 8/1/2023 to 8/15/2023 vs 7/1/2023 vs 7/15/2023)

If I select one month, it should show Selected Month MTD vs Previous Month MTD - if both have full months, then compare full month Count(ID) difference, if not, MTD Selected Month vs MTD Previous Month.

If I select two months, it should show two months MTD difference - if both have full months, then compare full month Count(ID) difference, if not, MTD Selected Month vs MTD Previous Month.

[daturpin]

For background, how are the months selected, are they stored in variables, and/or what is the form of table they are getting their data?

Off the bat I can see issues with the comparison you want because if today is March 30 then it will try to compare to February 30 and throw an error.

[qlikwiz123]

Any two random Months can be selected from a Filter. They are coming from the table directly in the script.

If Selected Month 1 has only 1st to 20th dates and Selected Month 2 has full dates (30), then it should only compare Month 1 IDs from 1st to 20th TO Month 2 IDs from 1st to 20.

If both of them have full dates generated, then it would be full month comparison. Not sure how it is an issue as that is an expected and fair comparison. 

[daturpin]

Well, just because February has 28 or 29 days and March has 31. So if you did your algorithm on March 30 it would throw an error.

Regardless, you can write a fairly complex expression to output what you want. For testing I made a Vizlib textbox and a table with timestamps (which I call game_time). In the text box I made the expression:

=If(Max([game_time.autoCalendar.Month]) - Min([game_time.autoCalendar.Month]) < 1, '0 month', If(Max([game_time.autoCalendar.Month]) - Min([game_time.autoCalendar.Month]) < 2, '1 month', 'other'))

If you select one date or several dates spanning less than a calendar month, it returns '0 month'. If you select dates that are greater than a month it returns '1 month', and anything more gives you 'other'

That should get you started. Basically you need to expand the algorithm by replacing the output strings with further calculations. autoCalendar.InMTD might be useful.

[qlikwiz123]

I think I am there with the logic but unable to apply the days selection to the older month.

Let's say I select May 2023 and August 2023, and my table only has data for August 2023 until 5th, I should compare Count(ID) from May 1st - 5th 2023 to August 1st to 5th 2023.

I am able to get date range for the latest month but not the older month.

Date(AddMonths(Min(Date),0), 'MM/DD/YYYY') gives me the starting date (05/01/2023) for the older month but I am unable to get the end date. If I can get the end date (05/05/2023) in this case, I should be able to build on top of it.

[daturpin]

My data is different than yours so it is hard to speculate, but the above expression simply gives me the minimum selected time, and subsequently swapping Min for Max would give me the last selected time.

[qlikwiz123]

Date(AddMonths(Min(Date),0), 'MM/DD/YYYY') gives me 05/01/2023 but when I do Date(AddMonths(Max(Date),0), 'MM/DD/YYYY'), it gives me 05/31/2023 but not 05/05/2023 which is the expected MTD.

[emhbrch1]

I was working on the same topic today and this works for my Data. It would basically select all the days in this month until today () as well as the same days from the past month for example 01.10-15.10. and 01.11.-15.11

If((InMonth(%Date, MonthStart(Today()), -1) and Day(%Date) <= Day(Today())) or (InMonth(%Date, MonthStart(Today()), 0) and Day(%Date) <= Day(Today())),1,0) AS [SameDayInPastMonthsAndCurrentMonth]

Maybe it helps you as well?