this can't be that difficult.. :)

[giakoum]

Hi all.

I have a set of sales. Every time some dimension of the sale changes, a new record is inserted with the change, mentioning new and old values. For example :

AA, HR, HRold, Value, ValueOld

1, 100, , 10,  

This is a new record so no old values

1, 110, 100, 10, 10

Same record changes HR (from 100 to 110) so old & new values are recorded as well and a new updated record is inserted in the table

Finally the data table looks like this :

1, 100,, 10,               (new)

1, 110, 100, 10, 10    (updated HR)

1, 110,, 9,                 (updated value)

2, 200,, 20,               (new)

2, 210, 200, 9, 20      (updated HR & Value)

3, 100, , 5,                (new)

4, 220, , 7,                (new)

Records with AA 1 and 2 have been updated, that is why they appear multiple times. Records 3 & 4 have not been updated, so they appear only once.

the result I need is for each HR code value that has been added (in) and value that has been removed (out) in one table:

HR     In     Out

100     15    -10

110     19    0

200     20    -20

210     9      0

220     7      0

Not possible to make calculations in the script as the actual app is much more complicated and the user needs to make selections and comparisons in the front end.

Attached is a sample application.

[tresesco]

Could not get the logic of (out).

[giakoum]

For record with AA 1, HR changed from 100 to 110. So it is -10 (Out) for 100, and +10 (In) for 110. Same for AA 2 : HR changed from 200 to 220, so it is -20 (Out) for 200 and +9 (In) for 220 (it is not +20 (in) for 220 because value changed from 20 to 9 at the same time).

Hope this makes sense!

[giakoum]

based on this discussion Evaluating "sets" in the context of a dimension

this solution works but it is unacceptable for the data volume in question :

If(HR=100 and not IsNull(HRold), Sum(TOTAL {$<HRold={"100"}>} MarginOld) * -1,

       If(HR=110 and not IsNull(HRold), Sum(TOTAL {$<HRold={"110"}>} MarginOld) * -1,

            If(HR=210 and not IsNull(HRold), Sum(TOTAL {$<HRold={"210"}>} MarginOld) * -1,

                 If(HR=220 and not IsNull(HRold), Sum(TOTAL {$<HRold={"220"}>} MarginOld) * -1,

                      If(HR=200 and not IsNull(HRold), Sum(TOTAL {$<HRold={"200"}>} MarginOld) * -1,

                      0

                 )

            )

       )

  )

)

This expression could be created in the script, so no need to hard code it, however its performance is ...bad! Any better idea?

[#5]

Have you tried a pick(match() ,....) solution to see how the performance looks like??

[Anonymous]

For In you could try Aggr(sum(Value), HR)

For out you could try Aggr(minstring(HR)-maxstring(HR), AA)

I'm less certain about the second one so someone else msy be able provide a better idea.

[giakoum]

Hi Nikos

still bad, as I have 4000 distinct values in HR and 30m records overall.

Thank you

[giakoum]

Thank you Bella, but this will not work in this case