Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Partial sums in a Pivot chart are wrong based on IF statement

Hi Guys,

I need your Help. I have the below expressions all based on complex Setanalysis in a pivot table

OEEcalc1.jpg

Now the thing is that I get the values like 148% which I have to limit to 100%

I tried with a if Statement as follows:

Performance = If ( setAnalysisExp >1,1, setAnalysisExp)

and the same way

Availability * Performance * Quality = if If ( setAnalysisExp >1,1, setAnalysisExp)

I get the table like this

OEEcalc2.jpg

But the Partial sums of Performance = 100% is wrong as it must be

Average of (81+100+93)  = 91 but not 100 as It shows here.


Any Idea how to solve this?

Thanks

Sravan

1 Solution

Accepted Solutions
Not applicable

Partial sums in a Pivot chart are wrong based on IF statement

Jason Michaelides :

Are you saying that 148% is the correct answer, but you want it to only show 100%

Jason

H Jason,

Thanks for the answer!

I meant the above Quoted! Though it gives 148%, I want to show it maximum as 100%

My if statement as well as your expression [RangeMin(1,setAnalysisExp)], works and gives 100% for the Performance Expression but the Partialsum in CHART does not work as I mentioned before.

I used Avg in the expression as below

Avg(Aggr(setAnalysisExp,BusinessUnit,Plant, Line))

to get the correct answer.

Thanks anyway for your help

Regards

Sravan

3 Replies
washington
Contributor

Partial sums in a Pivot chart are wrong based on IF statement

Put your application in order to take a look.

jason_michaelid
Honored Contributor II

Partial sums in a Pivot chart are wrong based on IF statement

Are you saying that 148% is the correct answer, but you want it to only show 100% if >100%? or is your problem that the expression should not evaluate to 148%?

If it is the former, try RangeMin(1,setAnalysisExp)

If it is the latter you will need to post your app so we can take a look.

Hope this helps.

Jason

Not applicable

Partial sums in a Pivot chart are wrong based on IF statement

Jason Michaelides :

Are you saying that 148% is the correct answer, but you want it to only show 100%

Jason

H Jason,

Thanks for the answer!

I meant the above Quoted! Though it gives 148%, I want to show it maximum as 100%

My if statement as well as your expression [RangeMin(1,setAnalysisExp)], works and gives 100% for the Performance Expression but the Partialsum in CHART does not work as I mentioned before.

I used Avg in the expression as below

Avg(Aggr(setAnalysisExp,BusinessUnit,Plant, Line))

to get the correct answer.

Thanks anyway for your help

Regards

Sravan

Community Browser