1 Reply Latest reply: Dec 17, 2017 5:37 AM by Mohammed Mukram Ali RSS

    Rolling over 45 days

    shweta gupta

      Hi Folks,

       

      Need help!

      I am stuck at two different things on Qlik Sense

      1. I have to calculate stock rolling over 45 days from maximum date

      Count({<Date={'>=$(vRolling45)<=$(vMaxim)'}>} Distinct SerialNo)                // This is done.

      where vMaxim =Date((Max(Date)),'DD-MM-YYYY')

                vRolling45=Date((Max(Date)-45),'DD-MM-YYYY')

      but now I have to calculate rolling over 45 days from maximum date -1

      For now i have created another variable 

                vMaxim_1 =Date((Max(Date)-1),'DD-MM-YYYY')

                vRolling45_1=Date((Max(Date)-46),'DD-MM-YYYY')

      expression changed to this:

      Count({<Date={'>=$(vRolling45_1)<=$(vMaxim_1)'}>} Distinct SerialNo) 


      Similarly,

      Count({<Date={'>=$(vRolling45_2)<=$(vMaxim_2)'}>} Distinct SerialNo) 

      Count({<Date={'>=$(vRolling45_3)<=$(vMaxim_3)'}>} Distinct SerialNo) 


      But I don't want to create these many variable, Can someone help me out to achieve this in any other way


      Problem 2:

      Now I want to show this on bar chart where my dimension will be dates of the current month

      value for maximum date(15-12-2017)

      Count({<Date={'>=$(vRolling45)<=$(vMaxim)'}>} Distinct SerialNo) // Stock as on date from last 45 days


      Value obtained from following expression on maximum date -1(14-12-2017)

      Count({<Date={'>=$(vRolling45_1)<=$(vMaxim_1)'}>} Distinct SerialNo)  //Stock as on previous date from its last 45 days


      and so on for the running month...

      How can I achieve this?

       

      Thanks in advance!!!