3 Replies Latest reply: Jun 23, 2016 9:45 AM by Tahlia Elcome RSS

    Intersection of dates less than selected date and null

    Tahlia Elcome

      Hi there,

       

      I am quite new to set analysis and I've been trying to tackle a problem for a few days and no luck.

       

      The task is quite simple.

       

      1st selection: Select date last sale and return everything less than that date and where date is null.

      2nd selection: Select data last purchase and return everything less than that date and where date is null.

       

      I found a few concepts on Qlik community which I am trying to apply and I've referred to a few resources as well as a manual on set analysis I've printed out but I really just need a bit of a hand here.

       

      So far I've basically used a date picker and used the following expression in my chart:

       

      sum({<DateLastSale={'<=$(=Date(vSaleDate))'}>} QtyOnHand)

       

      This works fine and returns what I'm expecting but now I'm not quite sure how to return those dates where the entries are null.

       

      I tried this to return null entries and this works on its own:

       

      sum({$-<[DateLastSale]={"=IsNull(DateLastSale) = 0"}>} QtyOnHand)

       

      I thought by creating an intersection of the two this might work but no such luck:

       

      sum({<DateLastSale={'<=$(=Date(vSaleDate))'}> * {$-<[DateLastSale]={"=IsNull(DateLastSale) = 0"}>} QtyOnHand)

       

      Please could I have some guidance on how to apply the first expression and apply an intersection to return null values as well.

       

      Thanks very much

        • Re: Intersection of dates less than selected date and null
          Manish Kachhia

          Have you created a Master Calendar?

          Can you provide sample file to work?

            • Re: Intersection of dates less than selected date and null
              Tahlia Elcome

              Hi Manish,

               

              Data is a confidential so it's going to be tricky posting this.

               

              The date last sale field is coming out of SQL along with QtyOnHand.

               

              SQL SELECT

              StockCode,

              Warehouse,

              QtyOnHand,

              CONVERT(VARCHAR(10),DateLastSale,110) as DateLastSale,

              CONVERT(VARCHAR(10),DateLastPurchase,110) as DateLastPurchase

              FROM $(schema).dbo.InvWarehouse

               

              Then I have a date picker and a chart as per the attached

               

              Capture - QV.PNG

               

               

               

              Where Date Last Sale is : =only({<DateLastSale={'<=$(=Date(vStartDate))'}>}DateLastSale)

              and Qty On Hand is : =sum({<DateLastSale={'<=$(=Date(vStartDate))'}>}QtyOnHand)

               

              Calendar picker setup as per below:

               

              Capture - Cal.PNG

               

              But now the issue I am having is I need to sum QtyOnHand where there is no date last sale but I am battling to achieve this. Basically I thought where len(DateLastSale) = 0?

               

              Let me know if you need anything else?

               

              Thanks again

              • Re: Intersection of dates less than selected date and null
                Tahlia Elcome

                Okay, I've applied this

                 

                =sum({<DateLastSale={'<=$(=Date(vStartDate))'}>}Salary) + sum({<DateLastSale= > - <DateLastSale= {'*'}>} Salary)

                 

                Which seems to be yielding the correct results at a glance. I need to do some tests though...

                 

                Not 100% sure this is entirely the correct way to go but I think I'm on the right track