topic Re: pivot able subtracting collumns in QlikView

pivot able subtracting collumns

dries_clairhout — Wed, 25 Nov 2020 16:16:04 GMT

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.

"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

Re: pivot able subtracting collumns

jensmunnichs — Mon, 29 Oct 2018 08:15:26 GMT

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.

Re: pivot able subtracting collumns

dries_clairhout — Mon, 29 Oct 2018 08:22:40 GMT

Hello,

Thanks for the fast respons,

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

Re: pivot able subtracting collumns

jensmunnichs — Mon, 29 Oct 2018 08:38:25 GMT

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

Re: pivot able subtracting collumns

dries_clairhout — Mon, 29 Oct 2018 08:50:41 GMT

Hey,

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

Re: pivot able subtracting collumns

Anil_Babu_Samineni — Mon, 29 Oct 2018 08:57:28 GMT

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)

Re: pivot able subtracting collumns

jensmunnichs — Mon, 29 Oct 2018 09:09:27 GMT

Check this out

https://community.qlik.com/docs/DOC-16454

Re: pivot able subtracting collumns

dries_clairhout — Mon, 29 Oct 2018 09:13:12 GMT

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!

Re: pivot able subtracting collumns

dries_clairhout — Mon, 29 Oct 2018 09:54:04 GMT

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

Re: pivot able subtracting collumns

Anil_Babu_Samineni — Mon, 29 Oct 2018 09:59:40 GMT

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

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