I am pretty sure what I want to achieve can be done via the use of Aggr()/RangeMax()/Above/Total functions... Just not able to figure out how. Here is the data and the result I want
|Time Stamp||ID 1||ID 2||ID 3||Value||Result|
|1/10/2017 2:00:00 PM||1||1||A1||100||100|
|1/10/2017 2:10:00 PM||1||1||A2||500||600|
|1/10/2017 2:15:00 PM||1||1||A2||800||900|
|1/10/2017 2:20:00 PM||1||1||A1||50||850|
|1/10/2017 2:20:00 PM||1||2||A3||100||950|
|1/10/2017 2:30:00 PM||1||1||A1||0||900|
|1/10/2017 2:30:00 PM||1||2||A4||50||950|
|1/10/2017 3:10:00 PM||1||2||A3||0||850|
|1/10/2017 3:30:00 PM||1||2||A5||1000||1850|
The result is basically the SUM(Value) of the MAX(TimeStamp) row grouped by ID1, ID2 and ID3 where TimeStamp <= TimeStamp of current row.
Simply put, for each row, do the following:
1. Get all the previous rows (TimeStamp of those rows is less than or equal to the TimeStamp of current row) for each group of ID1, ID2, and ID3 over current selections
2. From this set of rows, get the latest row in each group based on TimeStamp (of those rows)
3. From this set of rows Sum the Value and display in result field
The data load is ordered by TimeStamp, ID1, ID2, ID3. It can be changed if needed
Row 1: There are no previous values, so the Result is the Value
Row 2: There are 2 groups now and there is only 1 row for each so the Result is 100+500
Row 3: There are still 2 groups, the group with A1 has only one row, so 100. The group with A2 has 2 rows of which the one with timestamp 1/10/2017 2:15:00 PM will be considered, so 800. Hence the sum is 900.
So on and so forth...
Message was edited by: Prateek Somaiya Updated the table
can you please post the expression...
The reason I am asking for the expression is that i don't really have qlikview but have qiksense
i posted this thread in qlikview is because the community is big and and i have a better chance of finding an answer...
I think this will be difficult to do entirely in the front end. You might need to build a table of the max value for each group by date ([TimeStamp], ID1, ID2, ID3, Value). Something like:
LOAD [Time Stamp],
ID1, ID2, ID3,
If(ID1 = Previous(ID1) And ID2 = Previous(ID2) And ID3 = Previous(ID3), Value, Peek(MaxValue)) as MaxValue
Order by ID1, ID2, ID3, [Time Stamp];
(Rename the table name Data to the correct name for your requirements)
Now you can sum the MaxValues.
Not sure if I understand you correctly...
You are suggesting that I should keep the maxvalue, but I dont want the maxvalue, I want the value of the row with max timestamp for the group