Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

mahitham
Contributor

Excel calcualtions help

Hi Experts

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

Thanks in advance.

1 Solution

Accepted Solutions
MVP
MVP

Re: Excel calcualtions help

Here is a way of doing it in Qlik Sense:

2018-08-09 19_36_19-Film og TV.png

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.

7 Replies
MVP
MVP

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?

mahitham
Contributor

Re: Excel calcualtions help

Hi petter-s,

Thanks for your reply.

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.

Please help me on this.

Thanks in advance.

MVP
MVP

Re: Excel calcualtions help

Here is a way of doing it in Qlik Sense:

2018-08-09 19_36_19-Film og TV.png

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.

mahitham
Contributor

Re: Excel calcualtions help

Hi petter-s

Thanks for your help.

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.

,

MVP
MVP

Re: Excel calcualtions help

I would think so.

MVP
MVP

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...

MVP
MVP

Re: Excel calcualtions help

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

Community Browser