Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Alex
Contributor II
Contributor II

Dynamic Date Range in set analysis

Hi all,

I would like to calculate the devices deployed per Quarter so I have created a pivot table.

The problem comes when I select more than one quarter.
The InstallationDate Date range is wrong due to min/max dates which are 6 months (if I select 2 quarters), 9 or 12 depending on the number of quarters selected.

Set anlysis:
sum({< InstallationDate={"$(= '>=' & Date(MakeDate(Year(QuarterStart(min(MonthYear),0,11)), Month(QuarterStart(min(MonthYear),0,11))),'DD/MM/YYYY 00:00:00') &
'<' & Date(MakeDate(Year(QuarterStart(Addmonths(max(MonthYear),1),0,11)), Month(QuarterStart(addmonths(max(MonthYear),1),0,11))),'DD/MM/YYYY 00:00:00')
)"}, Month_Date={$(=vQuarterEndDates)} >}SerialNbrItemQty)

Attached some pics.

Thanks in advanced.


Alex.


4 Replies
sunny_talwar

Difficult to give the exact expression which will work without a sample... but try this...

Sum({<Month_Date={$(=vQuarterEndDates)}>}
Aggr(
If(
InstallationDate >= QuarterStart(Min(TOTAL <[Account Name], Country, Period> MonthYear),0,11) and InstallationDate < QuarterStart(AddMonths(Max(TOTAL <[Account Name], Country, Period>MonthYear), 1), 0, 11) , SerialNbrItemQty) , [Account Name], Country, Period, InstallationDate))
Alex
Contributor II
Contributor II
Author

Hi stalwar1,

Now, it's even incorrect for 1 quarter (pic attached).

The idea is to have the following formula per quarter (Nov is the first month):

Q1-FY18 - sum({< InstallationDate={">=2017/11/01<2018/02/01"}, Month_Date={'2018/01/01','2018/04/01','2018/07/01','2018/10/01'}, [Employee country of origin]=, OriginSubRegion=, CurrentMonthTracking= >}SerialNbrItemQty)

Q2-FY18 - sum({< InstallationDate={">=2017/02/01<2018/05/01"}, Month_Date={'2018/01/01','2018/04/01','2018/07/01','2018/10/01'}, [Employee country of origin]=, OriginSubRegion=, CurrentMonthTracking= >}SerialNbrItemQty)

etc.

Thanks in advance.

sunny_talwar

I wish that adding a picture was good enough to understand the whole data model and the issue you are running into. Unfortunately, it isn't. Unless you are able to share sample or sample data with expected output, I am afraid I won't be able to offer much help.

Best.
Sunny
Alex
Contributor II
Contributor II
Author

Hi stalwar1,

Attached the data model where I have a master calendar and the Fact table where SerialNbrItemQty and InstallationDate are.

Filtering only by Q4 it shows correct results because the Date range is getting min/max MonthYear values for 3 months.
However, if I filter by Q3 and Q4 min/max values take now 6 months range instead of first 3 for Q3 and second 3 for Q4 so numbers are incorrect.

I have added columns >= and < per quarter to check if the set analysy was calculating date ranges per quarter as expected, and it is. Don't know why it's taking both quarters as the date range for both Q3 and Q4 then.

Thanks.