topic Re: Find Cost in Table in QlikView

Find Cost in Table

Thu, 11 Oct 2012 06:02:33 GMT

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.

Re: Find Cost in Table

adhudson — Thu, 11 Oct 2012 06:55:16 GMT

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

Re: Find Cost in Table

Thu, 11 Oct 2012 07:10:37 GMT

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.

Re: Find Cost in Table

Fri, 12 Oct 2012 01:48:56 GMT

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

Re: Find Cost in Table

Fri, 12 Oct 2012 05:19:50 GMT

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

Regards,

Zain.

Re: Find Cost in Table

Fri, 12 Oct 2012 05:35:18 GMT

I got it Alex you define Alternate State.

Regards,

Zain.

Re: Find Cost in Table

Fri, 12 Oct 2012 05:44:17 GMT

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