Condition on the selected date range (previous month)

[Amit_B]

Hi,

How can I check if the previous month is part of the date range selected by the user?

I tried to use 4 variables:
* var_MinDate - min date selected
* var_MaxDate - max date selected
* var_PM_StartDate - start date previous month
* var_PM_EndDate - end date previous month

and using the following formula:
=If(var_MaxDate >= var_PM_StartDate or var_MinDate <= var_PM_EndDate, 1, 0)

My problem:
If the user selects for example September 2024 and November 2024, meaning the previous month (October 2024) is not selected by the user, yet the formula still returns 1.
=If(11/30/24 >= 10/01/24 or 09/01/24 <= 10/30/24, 1, 0) --> 1

I would appreciate help on this topic.
Thanks!

[marksouzacosta]

Hi @Amit_B,

How often you reload your application? Is it at least in a daily basis?
If so, you can create a new field in your table that flags your Date field as equal to previous month or not.

For example:

MyTable:
LOAD
// Load your fields
If(MonthStart(MyDateField) = MonthStart(AddMonths(Today(),-1)),1,0) AS IsPreviousMonth
FROM
// Your source
;

Now you can handle that in your Measures and UI in an easier way.
Following your example, if the users have selected September and November 2024 an expression like Sum(IsPreviousMonth) will return zero since October 2024 is not selected.

You can even use the IsPreviousMonth in Set Analysis to do other creative things.

I hope that helps!

For more information:

monthstart - script and chart function
https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/DateAnd...

addmonths - script and chart function
https://help.qlik.com/en-US/qlikview/May2024/Subsystems/Client/Content/QV_QlikView/Scripting/DateAnd...

Regards,

Mark Costa

[Amit_B]

The data is in a side table with two fields - Category and Amount.
The Amount refers only to the previous month (October).

The solution should not be in the script, but in the UI app.

[marcus_sommer]

It's not really clear what do you want to get. Yes, you could extend the (nested) if-loops to checks if there are gaps within the selections and if they may be the previous one to the min/max ones but it could become quite ugly. I suggest to avoid all such approaches and to teach the user just selects all periods without any gaps to get a valid view for this scenario. 

[Amit_B]

Thanks for the help.
I solved the problem with the P() function.