7 Replies Latest reply: Aug 9, 2018 3:32 PM by Petter Skjolden

# Excel calcualtions help

Hi Experts

Can any one please help me to calculate Calcolumn1,Calcolumn2, Calcolumn1% and Calcolumn2% in the below attached app.

• ###### Re: Excel calcualtions help

The formulas in the post text does not align with the formulas in the attached Excel sheet. Could you please either update your explanation or the Excel sheet so the questions makes sense and is consistent?

• ###### Re: Excel calcualtions help

Hi petter-s,

Sure I will try to update my text more clear.I am new to these calculations

Please consider the excel calculations only last 4 columns are the output columns which need to be calculated in Qlik.

• ###### Re: Excel calcualtions help

Here is a way of doing it in Qlik Sense:

Calcolumn1:

```=RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

```

Calcolumn2:

```=-RangeMin(0,Sum(Value)) +  RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1

```

Calcolumn1%:

```Sum(TOTAL <SCOPE>
Aggr(
RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
, Date, SCOPE, Name, Category )
)
/
Sum(TOTAL
Aggr(
RangeMax(0,Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
, Date, SCOPE, Name, Category )
)

```

Calcolumn2%:

```Sum(TOTAL <SCOPE>
Aggr(
RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
, Date, SCOPE, Name, Category )
)
/
Sum(TOTAL
Aggr(
RangeMax(0,-Sum(Value)) + RangeMax(Sum(Item1),Sum(Item2),Sum(Item3),Sum(Item4),Sum(Item5),Sum(Item6),Sum(Item7),Sum(Item8),Sum(Item9),Sum(Item10),Sum(Item11))/5+0.1
, Date, SCOPE, Name, Category )
)

```

I have attached the full example where the screenshot is taken from as a QVF-file for your testing...

I have taken the Excel table as is but I would highly recommend that this table should be unpivoted so the Item1...Item11 would appear as rows instead of columns in the table in Qlik Sense. Then the expressions in Qlik Sense would be different. I might give you a second Qlik Sense app to show how this is done - a little bit more evolved and a better solution in Qlik Sense to be honest.

• ###### Re: Excel calcualtions help

Hi petter-s

After converting into cross table from Item 1 to Item 11 is this possible to do the same expressions in backend.

with percentage has to be aggregated by SCOPE Field.

,

• ###### Re: Excel calcualtions help

I would think so.

• ###### Re: Excel calcualtions help

It will be much much less flexible and rather convoluted too - but doable.

• ###### Re: Excel calcualtions help

Actually my first suggestion for a solution contained some grave errors - so I have updated the post with correct expressions and an updated QVF too. If you have already downloaded the QVF be sure to delete that one and download again to get a correct one...