0 Replies Latest reply: Oct 28, 2016 6:26 AM by Chris Weldon RSS

    Choice of Date fields in Set Analysis

    Chris Weldon

      I have a Qlik Sense app, where a user selected a time period to investigate.  They have a choice of which date to use - so it might be the date the quote was received, it was issued, or it was agreed - using an extension to chose one of the three options, which are stored as a data island in the data model.  I am using a date picker to select the range of dates, and they are stored as a variables.

       

      Then I want to use set analysis to calculate the number of quotes issued in the period selected split by Region, and display in a pie chart. Before I added the complication of the choice of dates, the Set Analysis I used was:

       

      count(distinct{<DateQuoteSetUp={'>=$(date#(=vStartDate))'},

      DateQuoteSetUp={">=$(=date(vStartDate,'DD/MM/YYYY'))<=$(=date(vEndDate,'DD/MM/YYYY'))"},QuoteStatus={'Issued','Finalised'}>}[CaseID]),

       

      Now I want to replace the DateQuoteSetUp with logic that looks something like this:

       

      count(distinct{<$(if(GetFieldSelections(DateType)='Quote Set Up',DateQuoteSetUp,DateQuoteIssued))={'>=$(date#(=vStartDate))'},

      $(if(GetFieldSelections(DateType)='Quote Set Up',DateQuoteSetUp,DateQuoteIssued))={">=$(=date(vStartDate,'DD/MM/YYYY'))<=$(=date(vEndDate,'DD/MM/YYYY'))"},QuoteStatus={'Issued','Finalised'}>}[CaseID])

       

      But t doesn't seem to work - any ideas?

       

      I know I could build the logic outside the Set Analysis, but in other charts, the Set Analysis logic already has Ifs outside it so it would get very complicated and would be confusing to maintain.

       

      Many thanks

       

      Chris