topic Re: Difference between expressions using sum and total in QlikView

Difference between expressions using sum and total

Fri, 22 May 2015 06:03:57 GMT

Hello,

I am trying to understand a line graph with dimension = Period_id, where Period_id contains values of week numbers like 2015-10, 2015-11, 2015-12..

I'm trying to understand the difference between the below two expressions when used on the same graph. Are both trying to achieve the same thing?

=Sum(TOTAL<Period_id> FABS([SHIPMENT_FRCST_WEEK_N]-SHIPMENT_QTY))

=FABS(sum({$} [SHIPMENT_FRCST_WEEK_N]) - sum({$} SHIPMENT_QTY))

Thanks for your help

Re: Difference between expressions using sum and total

tresB — Fri, 22 May 2015 06:18:07 GMT

If the word total comes in an expression, the calculation will be made over all possible values given the current selections, but disregarding the chart dimensions. Hence for your TOTAL<Period_id> part of expression, the expression result will be given without considering the vaules of Period_id dimension (all same across Period_id). Hope this helps.

Re: Difference between expressions using sum and total

danieloberbilli — Fri, 22 May 2015 06:18:59 GMT

Sum(TOTAL<Period_id> FABS([SHIPMENT_FRCST_WEEK_N]-SHIPMENT_QTY))

sum of absolute value of variance (field1-field2) with disregarding all dimensions except Period_id

FABS(sum({$} [SHIPMENT_FRCST_WEEK_N]) - sum({$} SHIPMENT_QTY))

absolute value of difference between total sum of field1 and total sum of field2

not sure what the intention of each one was

Re: Difference between expressions using sum and total

Fri, 22 May 2015 06:38:53 GMT

Hello All,

thanks for your replies. The graph should calculate the following KPI with dimension = Period_id.

The portion of code written is trying to achieve the (Absolute of SHIPMENT_FRCST_WEEK_N - SHIPMENT_QTY)

Which one of the 2 following is correct?

=Sum(TOTAL<Period_id> FABS([SHIPMENT_FRCST_WEEK_N]-SHIPMENT_QTY))

=FABS(sum({$} [SHIPMENT_FRCST_WEEK_N]) - sum({$} SHIPMENT_QTY))

Re: Difference between expressions using sum and total

danieloberbilli — Fri, 22 May 2015 06:43:58 GMT

actually, you should try this out with some very simple sample data in Excel so that you exactly see what is happening. If you are not familiar with the expressions, there might be even more pitfalls to consider in developing the right approach for your objective.

Re: Difference between expressions using sum and total

Fri, 22 May 2015 06:47:43 GMT

Hi Tresesco,

You mean that on the chart where x-axis value of Period_id = 2015-10, the y-value displayed will be the total of the difference between SHIPMENT_FRCST_WEEK_N and SHIPMENT_QTY fields for all existing Period_ids present in the qlikview application when i use the below:?

=Sum(TOTAL<Period_id> FABS([SHIPMENT_FRCST_WEEK_N]-SHIPMENT_QTY))