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

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

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