How to Calculate weekends in Calendar for different Min and Max date?

[mohan_1105]

HI,

I wanted to calculate the Probability of weekend shopping behavior of each customer in my data. My formula is

Total Weekends the customer shopped / Total weekend passed from is first purchase date to last purchase date.

I can able to calculate the Numerator but for denominator I dunno how to write the expression that captures Min and Max date of each customer.

I am using straight table and what I tried is, I created the master calendar in backend and written the set analysis like,

count({<[Cal.Weekday] = {'Sat','Sun'},[Cal.Date]={'>=$(Min(BillDate))<=$(Max(BillDate))'}>} [Cal.Date]). I thought Qlik will understand Min and Max date differs for each Customer I have but it did not. It captures least and the last of generated Master calendar date and gives me calculated total weekends between the first and late date.

I have attached the sample file with discussion for your convenience. Thank you all !!! Looking for your help and let me learn Qlik.

[sunny_talwar · Accepted solution]

Is this what you want?

Try this:

Count({<[Cal.Weekday] = {'Sat','Sun'}>} Aggr(If([Cal.Date] >=Min(BillDate) and [Cal.Date] <= Max(BillDate), [Cal.Date]), CustomerCode, [Cal.Date]))

[mohan_1105]

Hi Sunny,

Thank you, Yeah, my expected output should look like that. I know that I have to use Aggr function somewhere in the expression. Now I got it and Can you please help to understand your expression why did you use Cal.Date inside the Aggr function?

[sunny_talwar]

Since you are looking to compare two tables which are not connected in anyway, you have to somehow make that connection. To do this, I basically used the most granular level from your fact table which is used in the expression and most granular level from the calendar table which is used in the expression.

To see the effect, create a chart with two dimensions

CustomerCode

Cal.Date

and expression

If([Cal.Date] >=Min(BillDate) and [Cal.Date] <= Max(BillDate) and Match([Cal.Weekday], 'Sat', 'Sun'), 1, 2)

You will see a table with Cartesian join, with 1 for all the places you need to be counted and 2 for the places you don't want to count.

[mohan_1105]

Yeah!! Got how it works,

If the dimension is not from the same table then we have to find the connection between two tables. And use it along with the dimension I need to aggregate like you did the solution expression. Right?

[sunny_talwar]

Yup, something like that

[mohan_1105]

Sunny,

I have applied the expression in my actual table, which has more than 60,000 CustomerCode. The output of the expression is '-'. And also It lags with speed. Is there is any other way to achieve it??

[sunny_talwar]

May be do this count in the script itself if this doesn't have to change based on selection or connect your Bill Date and Cal.Date and then work with it... will have to test those things out before I can be sure of a definite solution