How do I disregard lower date boundary?

[#1]

Hi there!

I am fairly new to Qlik and trying to create an expression that let's me calculate distinct customer count between 1/1/2016 and a given date (basically YTD). The user would select start date and end date (e.g., May 1, 2016 - May 31,2016). I am trying to see a) how you can get YTD count from 1/1/2016 and your upper date range (e.g. May 31, 2016) and b) how you would calculate the difference between the YTD count from 1/1/2016 to lower date range (May 1, 2016) YTD count from 1/1/2016 to upper date range. Any help would be great!

I've tried the following below:

Count({<[Transaction_Date.autoCalendar.Year]= ,

[Transaction_Date.autoCalendar.Quarter]= ,

[Transaction_Date.autoCalendar.Month]= ,

[Transaction_Date.autoCalendar.Week]= ,

Transaction_Date = {"$(='>=' & Date(AddYears(YearStart(Max(Transaction_Date)), -1),'DD/MM/YYYY') & '<=' & Date(AddYears(Max(Transaction_Date), -1), 'DD/MM/YYYY'))"}>} Distinct Customer_ID)

[swuehl · Accepted solution]

This does look quite correct to me (note that you are using Addyears() to actually get the last year to date), if the format code 'DD/MM/YYYY' matches the format of your Transaction_Date field and if you then select a date in that field, what do you get returned?

Zero, NULL or something else? what do you expect to see for that date range?

YTD Max Date:

Count({<[Transaction_Date.autoCalendar.Year]= ,

[Transaction_Date.autoCalendar.Quarter]= ,

[Transaction_Date.autoCalendar.Month]= ,

[Transaction_Date.autoCalendar.Week]= ,

Transaction_Date = {"$(='>=' & Date(YearStart(Max(Transaction_Date)),'DD/MM/YYYY') & '<=' & Date(Max(Transaction_Date), 'DD/MM/YYYY'))"}>} Distinct Customer_ID)

YTD Min Date:

Count({<[Transaction_Date.autoCalendar.Year]= ,

[Transaction_Date.autoCalendar.Quarter]= ,

[Transaction_Date.autoCalendar.Month]= ,

[Transaction_Date.autoCalendar.Week]= ,

Transaction_Date = {"$(='>=' & Date(YearStart(Min(Transaction_Date)),'DD/MM/YYYY') & '<=' & Date(Min(Transaction_Date), 'DD/MM/YYYY'))"}>} Distinct Customer_ID)

For the diff calculation (if min and max are in the same year):

YTD Max Date:

Count({<[Transaction_Date.autoCalendar.Year]= ,

[Transaction_Date.autoCalendar.Quarter]= ,

[Transaction_Date.autoCalendar.Month]= ,

[Transaction_Date.autoCalendar.Week]= ,

Transaction_Date = {"$(='>=' & Date(Min(Transaction_Date),'DD/MM/YYYY') & '<=' & Date(Max(Transaction_Date), 'DD/MM/YYYY'))"}>} Distinct Customer_ID)

See also

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

[sunny_talwar]

As you can see from Stefan's code, he has removed AddYears() function. AddYear( ... , -1) is used if you wish to go back on years (essentially looking at 1/1/2015 - 5/31/2015 in the example you provided above).

[swuehl]

You might have also noted that the third expression I suggested might not return the same result than just calculating the difference of the first two expressions, because of the distinct customer count.

(For example, if you have the exact same set of customers appear each month, the difference of the first two expressions would return with your sample selection, while the third expression would return the same as the first two separately.)

[#5]

Sorry for the delay in response - this was great swuehl‌! Thank you so much.

[#6]

Thank you so much!!!