Advanced Aggregation (RangeMax/Above/Total/Aggr)

[#1]

Hi

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

Example:

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...

Please help!

Message was edited by: Prateek Somaiya Updated the table

[sunny_talwar]

Should row 4 be 850 instead of 950 because A1 for max date tune is 50 and A2 for max date time is 800 adding up to 850?

[#3]

Thanks for pointing out the error Sunny, I have updated the table

[sunny_talwar]

You may not like the technique, but it seems to be working (last row is different, do you think 1850 is right?)

[sunny_talwar]

I did not think this through... ID 3 can be more than you have listed above, right? My solution might not really work for you

[#6]

‌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...

[#7]

I looked at your expressions and you are right, I don't like it

You are hard-coding the value of IDs, that's not going to fly... If you ask me that's a sin!!

[jonathandienst]

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:

Join(Data)

LOAD [Time Stamp],

    ID1, ID2, ID3,

    If(ID1 = Previous(ID1) And ID2 = Previous(ID2) And ID3 = Previous(ID3), Value, Peek(MaxValue)) as MaxValue

Resident Data

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.

[#9]

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

[#10]

swuehl‌ , I have seen a lot of your posts and replies on the forum and learned a lot from them. I am sure that you will be able to help. Can you please check it out once

Thanks