Skip to main content
Announcements
SYSTEM MAINTENANCE: Thurs., Sept. 19, 1 AM ET, Platform will be unavailable for approx. 60 minutes.
cancel
Showing results for 
Search instead for 
Did you mean: 
dries_clairhout
Creator
Creator

pivot able subtracting collumns

Dear users,

I have to calculate the difference between 2 values in a pivot table.

I am fairly new to qlik and could use some advice.

dd.png

"Ontvangen ST" should be subtracted by "Afgifte ST" the result shoul be in Voorraad ST.

Ontvangen ST:

= sum({<DatumType={'Inkoop datum'},POrderDate={">=$(=date($(vStartDate)))<=$(=date($(vEndDate)))"},Jaar=,Week=,Maand=>}ReceivedQty)

Afgifte ST:

sum({<DatumType={'InvHist datum'},POrderDate={">=$(=date($(vStartDate)))<=$(=date($(vEndDate)))"},Jaar=,Week=,Maand=>} Qty_Afgifte)

Can someone help me?

"Ontvangen ST" is the recieved Qty

"Afgifte ST" is the exported Qty

"Voorraad KG" is supply qty (so it should be Recieved Qty - Exported Qty)

I looked at every previous discussion and nothing works.

Please help.

Thanks in afvance

1 Solution

Accepted Solutions
Anil_Babu_Samineni

Try as simple?

sum({<DatumType={'Inkoop datum'},POrderDate={">=$(=date($(vStartDate)))<=$(=date($(vEndDate)))"},Jaar=,Week=,Maand=>}ReceivedQty) -

sum({<DatumType={'InvHist datum'},POrderDate={">=$(=date($(vStartDate)))<=$(=date($(vEndDate)))"},Jaar=,Week=,Maand=>} Qty_Afgifte)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

View solution in original post

9 Replies
jensmunnichs
Creator III
Creator III

You can reference the value of another expression in the same chart simply by referring to the expression's label. So in this case you'd simply use [Ontvangen ST] - [Afgifte ST]. If that doesn't work let me know.

dries_clairhout
Creator
Creator
Author

Hello,

Thanks for the fast respons,

but I get absolutely no return when I add this expression.

jensmunnichs
Creator III
Creator III

Could you attach your application? Would make this a lot easier to solve.

dries_clairhout
Creator
Creator
Author

Hey,

I don't know how I would do this correctly?

Anil_Babu_Samineni

Try as simple?

sum({<DatumType={'Inkoop datum'},POrderDate={">=$(=date($(vStartDate)))<=$(=date($(vEndDate)))"},Jaar=,Week=,Maand=>}ReceivedQty) -

sum({<DatumType={'InvHist datum'},POrderDate={">=$(=date($(vStartDate)))<=$(=date($(vEndDate)))"},Jaar=,Week=,Maand=>} Qty_Afgifte)

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
dries_clairhout
Creator
Creator
Author

Anil,

Thanks,

I did have to use a + instead of -,

because afgite is a negative value,

but that works!!

I do have an additional question,

I have duplicate data as you can see above (the white line no title)

But the data is nog the same.

This probably isn't an easy fix.

Also another question, for the calculation of KG another qlik consultant used this:

=sum({<DatumType={'Inkoop datum'},POrderDate={">=$(=date($(vStartDate)))<=$(=date($(vEndDate)))"},Jaar=,Week=,Maand=>} ReceivedQty*DimA*DimB/1000000) * GSM / 1000

I don't get this. and results sometimes display, but sometimes they don't any hints?

Thanks a lot!

dries_clairhout
Creator
Creator
Author

Thanks a bunch!! when i replaced the - with a + it worked like a charm.

Anil_Babu_Samineni

could be there is no data after multi selection for this period

POrderDate={">=$(=date($(vStartDate)))<=$(=date($(vEndDate)))"}

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful