Hello Community,
I am currently trying to migrate a report from Tableau to Qlik Sense, but I have some trouble understanding how to approach some date intervals.
I need a filter to select 'Last 3 Months', 'Last 6 Months', 'Last 12 Months',
Let's take an example: What should 'Last 12 Months' selection do? This should affect some calculations for two category of date intervals: Selected Timeframe vs Comparison Timeframe , as following:
Selected timeframe has 2 variables: start_date_ST_12M =Monthend(AddMonths(run_date,-1)), end_date_ST_12M =AddMonths(MonthStart(AddYears(start_date_ST_12M,-1)),+1) /*where run_date is the date when the DB was refreshed*/
Comparison timeframe has 2 variables: start_date_CT_12M =AddYears(start_date_ST_12M,-1), end_date_CT_12M=AddYears(end_date_ST_12M,-1)
The problem is that when I have to select Last 3 Months or Last 6 Months, the variables created above are no longer correct so have created variables (4 for each category) with AddMonths to match the criterias.
Then, what I did so far was to create a master dimension which looks like this, in order to create a timeframe filter:
= IF(Lead_Date>= start_date_ST_3M and Lead_Date <= end_date_ST_3M, 'Last 3 months through last month',
if(Lead_Date>= start_date_CT_3M and Lead_Date <= end_date_CT_3M, 'Last 3 months through last month',
if(Lead_Date>= start_date_ST_6M and Lead_Date <= end_date_ST_6M, 'Last 6 months through last month',
if(Lead_Date>= start_date_CT_6M and Lead_Date <= end_date_CT_6M, 'Last 6 months through last month',
if(Lead_Date >= start_date_ST_12M and Lead_Date <= end_date_ST_12M, 'Last 12 months through last month',
if(Lead_Date >= start_date_CT_12M and Lead_Date <= end_date_CT_12M,'Last 12 months through last month',
)))))
)
I have created another master dimension in order to get a selected timeframe vs comparison timeframe using an if, but this is not correct, because the variables are hardcoded and I wish that the start and end dates to be affected by what the user selects in the filter above.
= IF(Lead_Date >= start_date_ST_12M and Lead_Date <= end_date_ST_12M, 'Selected Timeframe',
if(Lead_Date >= start_date_CT_12M and Lead_Date <= end_date_CT_12M,'Comparison Timeframe'))
Actually, the formula above works, but when I select Last 12m, I get only values for 6M (excluding the lead_dates from last6m and last3m filters).
Is there another way on how to approach these kind of situation?
Any ideas will be appreciated. Thank you in advance!
