topic Re: Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match? in QlikView

Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

Peter_Brunner — Thu, 08 Sep 2016 08:17:15 GMT

Hi Guys,

I have been trying to work out why I cannot get two totals to match

I have two tables:

A straight table with a check Column (expression) which gives correct results for subtracting across columns.

"Notice I am not using any aggregation e.g.: sum , count in expression but this is gives correct results"!

=if((Column(2)-Column(4)-Column(5)-Column(6))<0,0,(Column(2)-Column(4)-Column(5)-Column(6)))

so the straight table columns are pointing to each columns expression as below

(Column(2) = SL_ON_HAND_QTY -

Column(4) = sum({$<TRANS_TYPE={'DRINV','DRCDT'}>}ENTERED_QTY) -

Column(5)= sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY) -

Column(6) = sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'TTT','CCC'}>}ENTERED_QTY)

and another column with =$(vSlowQty) the problem variable.

Now I have another table an aggregated summary table which is using same variable in expression

=Sum(Aggr($(vSlowQty),STOCK_CATEGORY,STOCK_LOCATION,STOCK_CODE)

as we still need to be able to select on 3 Dimensions which the above formula does

I have used the variable $(vSlowQty) in both tables which is subtracting the Columns as above

so $(vSlowQty) =

if(SL_ON_HAND_QTY-

sum({$<TRANS_TYPE={'DRINV','DRCDT'}>}ENTERED_QTY) -

sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY) -

sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'TTT','CCC'}>}ENTERED_QTY)<0,0,

SL_ON_HAND_QTY-sum({$<TRANS_TYPE={'DRINV','DRCDT'}>}ENTERED_QTY) -

sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY) -

sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'TTT','CCC'}>}ENTERED_QTY))

I have tried using count instead of Sum its gets total closer but still no match what am I missing here ?

any help appreciated

regards Peter

Re: Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

kaushiknsolanki — Thu, 08 Sep 2016 08:21:51 GMT

Hi,

Can you attach sample data to understand the issue in detail.

Regards,

Kaushik Solanki

Re: Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

sunny_talwar — Thu, 08 Sep 2016 09:30:26 GMT

Your first expression isn't using Sum(Aggr(...)) function, but still giving you the right total, why use it for the second expression? May be try just vSlowQty or $(vSlowQty) as your expression.

Re: Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

Peter_Brunner — Fri, 09 Sep 2016 07:17:27 GMT

Hi Guys,

I have attached a sample I put together it has more explanation in app

Sunny I need a variable to reflect the results the way the simple formula below works in main table

if((Column(2)-Column(4)-Column(5)-Column(6))<0,0,(Column(2)-Column(4)-Column(5)-Column(6)))

maybe I should not use set analysis with subtractions ? but I need to use this in a Variable so I can use this in 2 different tables one of which uses Aggr

thanks Peter

Re: Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

sunny_talwar — Fri, 09 Sep 2016 07:32:34 GMT

Check with this as your variable:

If(

SL_ON_HAND_QTY-

$(vSalesQty)-

Sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'KWH','C33'}>}ENTERED_QTY)-

Sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY)<0,0,

SL_ON_HAND_QTY-

$(vSalesQty)-

Sum({$<TRANS_TYPE={'STTRF'},STOCK_LOCATION={'KWH','C33'}>}ENTERED_QTY)-

Sum({$<TRANS_TYPE={'BMTRF'}>}ENTERED_QTY))

Re: Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

Peter_Brunner — Fri, 09 Sep 2016 07:40:01 GMT

Hi Sunny,

wow that was quick! and Thanks

now explain why this works the way you did this?

Peter

Re: Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

sunny_talwar — Fri, 09 Sep 2016 07:42:56 GMT

I think your Column(2) was having an if statement, whereas your variable did not include the if statement within your variable when you were checking if the Col(2) - Col(4) - Col(5) - Col(6) > 0. Once I fixed Col(4) to match what you had in Col(4), the expression started working.

Re: Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

Peter_Brunner — Mon, 12 Sep 2016 00:12:38 GMT

Hi Sunny,

I had a good look at what I did and you are right

thanks again

Peter

Re: Aggregated table and Straight table with Set Analysis Subtraction Formula's Totals don't match?

sunny_talwar — Mon, 12 Sep 2016 00:21:53 GMT

I am glad you were able to grasp the issue that was causing problem here

Best,

Sunny