Find Cost in Table

[#1]

Hi,

I have data which is given below format and I want to find out deleted and new assets between to months,

so table rows in red is deleted and in green is new assets in table

Asset id	Group	Cost	Month
12	a	100	8/31/2012
13	a	200	8/31/2012
14	b	300	8/31/2012
15	a	200	8/31/2012
16	b	150	8/31/2012
12	a	100	9/30/2012
13	a	200	9/30/2012
50	b	600	9/30/2012
51	a	700	9/30/2012
16	b	150	9/30/2012

This is my date selection from list

Current Date	Comparision Date
8/31/2012	9/30/2012

Requried out is given below:

Group	Deleted Cost	New Cost
a	200	700
b	300	600
Total	500	1300

Please let me the expression for above requried output.

Regards,

Zain.

[adhudson]

Hi Zain,

     I suggest, to add column to indicate the previous asset id of the new asset id.

     Then it ll be easy for you to get the solution.

     Otherwise, tell me if the order of the rows are constant?

Regards

Andrew Hudson

[#3]

Thanks for your reply, I am already working on it through mapping and its almost done, but I want to know the possibility on expression level, Yes rows are constant on order by of Asset id and date.

[#4 · Accepted solution]

Hi,

I'm not sure if this is what you need but see attached qvw.

I used alternate state on my 2nd list box (Comparison) to have 2 listbox selection from the same Field.

Regards,

Alex

[#5]

Thanks you very much Alex, just let me know what is {alter1} ?

Regards,

Zain.

[#6]

I got it Alex you define Alternate State.

Regards,

Zain.

[#7]

Hi

I just named the alternate state I created as "alter1".

you can create your own in [Settings > Document Properties > General > Alternate States].

then on my second listbox which I named "Comparison" I selected alter1 under [General > Alternate State]

then on my pivot or straight table, my second expression is sum({alter1}Cost) which will force it to only react on my alter1 selection/s (which in this case is the selection I made on my Comparison Listbox)

Hope that explains it.

Regards,

Alex