
Re: Excel calcualtions help
Petter Skjolden Aug 9, 2018 12:32 PM (in response to Mahitha M)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
Mahitha M Aug 9, 2018 12:38 PM (in response to Petter Skjolden )Hi petters,
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.

Re: Excel calcualtions help
Petter Skjolden Aug 9, 2018 1:40 PM (in response to Mahitha M)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 QVFfile 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
Mahitha M Aug 9, 2018 1:20 PM (in response to Petter Skjolden )Hi petters
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.
,

Re: Excel calcualtions help
Petter Skjolden Aug 9, 2018 1:35 PM (in response to Mahitha M)I would think so.

Re: Excel calcualtions help
Petter Skjolden Aug 9, 2018 3:32 PM (in response to Petter Skjolden )It will be much much less flexible and rather convoluted too  but doable.


Re: Excel calcualtions help
Petter Skjolden Aug 9, 2018 1:41 PM (in response to Mahitha M)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...



