topic Re: Subtracting individual rows & create a new column in QlikView

Subtracting individual rows & create a new column

MK9885 — Sat, 16 Nov 2024 20:17:36 GMT

I'm trying to subtract 2 different rows in same column to create a new column.

These are 2 specific columns

Ex: RowE-RowK = New Column & the result from this should only reflect in RowF

Note: There are more than 1 Row named as RowE & RowK

I do not need totals.. I tried below solution.

If( Dim1='G', Sum(total {<Dim1={'C'}>}Amount)-Sum(total {<Dim1={'G'}>}Amount),

If( Dim1='F', Sum( total {<Dim1={'F'}>}Amount)-Sum(total {<Dim1={'B'}>}Amount), Sum(Amount))

)

Since Total is used, the result only shows difference between full total but I'd like to get individuals when nothing is selected.

@sunny_talwar

Re: Subtracting individual rows & create a new column

sunny_talwar — Fri, 09 Aug 2019 16:22:05 GMT

Do you have another dimension in your chart? I think Dim1 is one of the dimension... what is the other one?

Re: Subtracting individual rows & create a new column

MK9885 — Fri, 09 Aug 2019 16:29:50 GMT

Yes thats the example I tried..
I'm using below exp

If( Dim1='10 -Fee1', Sum(total {<Dim1={'06 - AR'}>}Dollars)-
Sum(total {<Dim1={'09 - Bad AR'}>}Dollars))

Where ever there is a row '10-Fee1' I want to subtract 06-AR & 09-Bad AR

If I select a filter, it gives me what I need but with a duplicate value

When I clear filters, I get a TOTAL which I do not want

Cus there are more than 1 06-AR & more than 1 09-BadAR

Re: Subtracting individual rows & create a new column

sunny_talwar — Fri, 09 Aug 2019 16:45:54 GMT

I don't think you answered what I asked 🙂

Re: Subtracting individual rows & create a new column

MK9885 — Fri, 09 Aug 2019 17:00:55 GMT

There are like 6 dimensions

Re: Subtracting individual rows & create a new column

MK9885 — Mon, 12 Aug 2019 15:07:22 GMT

Any suggestion?

Thanks

Re: Subtracting individual rows & create a new column

sunny_talwar — Mon, 12 Aug 2019 15:54:02 GMT

With 6 dimensions, it would be difficult to respond back because I don't really know what you have and what you real want. Unless you are able to provide a sample file with expected output, I am not really confident that I will be able to help.

Re: Subtracting individual rows & create a new column

MK9885 — Mon, 12 Aug 2019 17:27:51 GMT

If you could at least tell me how to subtract 2 different rows from Column A and create a new Column B?

I used TOTAL which only gives me correct ans if I select that particular line item.

If I clear the selection it gives me huge number which I do not want

Ex: I need to see 229

With filter selection I get 229

When I clear filter, I get 878,562 for all the rows.. It is like duplicate

Re: Subtracting individual rows & create a new column

MK9885 — Mon, 12 Aug 2019 23:06:06 GMT

I think I got the result...

It wasn't the expression. It was applymap excel file.

Was reading it from 2 different sources & one of them was incorrect/not matching other file hence wrong numbers.

Thanks for your help!