CODEWITHSUNDEEP
qliksense
clairestreb
clairestreb

Reputation: 1291

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:

UPDATE: I have done more work. Given my table, how can I look up the values to use in my pivot table's expression when the user filters on [Transaction Date]? Please see my newest data and charts at http://clairenstreb.brinkster.net/temp/InventoryValues.png

Upvotes: 1

Views: 2346

Answers (1)

clairestreb
clairestreb

Reputation: 1291

The answer was to use the firstsortedvalue chart function. For example:

firstsortedvalue ([Beginning Inventory Amount], -[Beginning Inventory Date])

Upvotes: 1

Related Questions