0 Replies Latest reply: Jun 15, 2017 7:37 PM by Claire Streb RSS

    How to get start and end values based on date(s) in a Qlik Sense Visualization expression?

    Claire Streb

      I am new to Qlik and just can't seem to wrap my head around this challenge I have at work.  Any ideas????????!???!?!?!?!?!

       

      In a visualization (in the standard hub), I need to perform a calculation based on data in two tables and date(s) the user selects from a list of DateAs in a Filter pane.

       

       

      GIVEN
      Given two tables below.  There is more data in them, I am just showing the data for primary key = 2.

       

       

      Table A:
      PrimaryKeyA | ValueA | DateA
      2           | 300.00 | 2/1/2017
      2           | 100.00 | 2/28/2017
      2           | 123.45 | 3/1/2017
      2           |   0.02 | 3/15/2017
      2           |   0.03 | 3/31/2017
      2           |   0.01 | 5/1/2017
      2           |  67.89 | 6/2/2017

       

       

      Table B:
      PrimaryKeyB | ValueB | DateB
      2           | 9.87   | 2/28/2017
      2           | 9.86   | 3/31/2017
      2           | 9.85   | 4/31/2017
      2           | 9.84   | 5/31/2017


      CALCULATION BASED ON FILTERED DATES
      Result = FirstValue + Sum(ValueA) - SecondValue


      CALCULATION RULES, WHERE PrimaryKeyA = PrimaryKeyB
      1. If only      one date is selected in the Date filter, the SecondValue is ValueB whose DateB is on or the closest before the        filtered date.
      2. If more than one date is selected in the Date filter, the SecondValue is ValueB whose DateB is on or the closest before the latest filtered date.

       

       

      3. If only      one date is selected in the Date filter, the FirstValue is ValueB whose DateB is the closest before SecondValue's DateB.
      4. If more than one date is selected in the Date filter, the FirstValue is ValueB whose DateB is the closest before the earliest filtered date.

       

       

      5. When a match is not found, set the value (FirstValue or SecondValue) to zero.


      EXAMPLES

       

      1. The user selects the date filter of:  6/2/2017

       

       

         SecondValue is 9.84 (matching 5/31/2017 from Rule 1)
         FirstValue  is 9.85 (matching 4/31/2017 from Rule 3)
        
         Result = 9.85 + 67.89 - 9.84 = 67.9

       

       

      2. The user selects the date filter of: 5/1/2017

       

       

         SecondValue is 9.85 (matching 4/31/2017 from Rule 1)
         FirstValue  is 9.86 (matching 3/31/2017 from Rule 3)
        
         Result = 9.86 + 0.01 - 9.85 = 0.02

       

       

      3. The user selects the date filter of: 3/1/2017

       

       

         SecondValue is 9.87 (matching 2/28/2017 from Rule 1)
         FirstValue  is 0    (no match           from Rule 5)
        
         Result = 0 + 123.45 - 9.87 = 113.58
        
      4. The user selects the date filter of: 3/1/2017 through 3/31/2017

       

       

         SecondValue is 9.86 (matching 3/31/2017 from Rule 2)
         FirstValue  is 9.87 (matching 2/28/2017 from Rule 4)
        
         Result = 9.87 + (123.45 + 0.02 + 0.03) - 9.86 = 123.51
        
      5. The user selects the date filter of: 3/1/2017 through 6/2/2017

       

       

         SecondValue is 9.84 (matching 5/31/2017 from Rule 2)
         FirstValue  is 9.87 (matching 2/28/2017 from Rule 4)
        
         Result = 9.87 + (123.45 + 0.02 + 0.03 + 0.01 + 67.89) - 9.84 = 191.43

       

       

      6. The user selects the date filter of: 2/1/2017 through 2/28/2017

       

       

         SecondValue is 9.87 (matching 2/28/2017 from Rule 2)
         FirstValue  is 0    (no match           from Rule 5)
        
         Result = 0 + (300.00 + 100.00) - 9.87 = 390.13


      IDEAS
      1. I don't think Qlik Sense has user-defined functions, so that idea is out.

       

       

      2. I was thinking of an expression that would perform the following pseudo code, but I have no idea how this could be accomplished.

         Set MaxDateFilter = MAX(Filtered dates)
         Set MinDateFilter = MIN(Filtered dates)
         If MaxDateFilter = MinDateFilter
         Then   (One date selected)
                Set SecondValue = Lookup first match in TableB where DateB =< MaxDateFilter (If no match found, set to 0)
                Set FirstValue = Lookup first match in TableB where DateB < SecondValue's DateB (If no match found, set to 0)
         Else   (Date range selected)
                Set SecondValue = Lookup first match in TableB where DateB =< MaxDateFilter (If no match found, set to 0)
                Set FirstValue = Lookup first match in TableB where DateB < MinDateFilter(If no match found, set to 0)
         Result is FirstValue + SUM(ValueA) - SecondValue
        
      3. I was thinking of dynamically creating a new TableC from a subroutine that contains the possible dates, based on MIN(DateA and DateB) and had ValueBs in it somehow, maybe all of the possible permutations, but I am lost.

       

       

      4. I was thinking it was impossible to find out what the user selected in the filter anyway, but what do I know.

       

       

      5. I looked at this(https://community.qlik.com/thread/16998), but I think it is only QlikView, not Qlik Sense.

       

       

      THINGS I HAVE TRIED / RESEARCHED

       

      Since I am new to Qlik, I do not even know where to begin with this one.
      Here is what I've tried to no avail:

       

      Message was edited by: Claire Streb to fix typo