7 Replies Latest reply: May 23, 2017 8:14 AM by Sunny Talwar RSS

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

    Mohan Kumar

      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.