6 Replies Latest reply: Mar 14, 2018 6:58 PM by David Forest

# KPI and Bar Chart Total not matching

I am having KPI having following condition

KPI:

if (sum([Quantity]) <> 0,

if (Sum([DailyQuantitySales]) <> 0,

sum([DailySales]) - sum([DailyVarSales]),

sum([DailySales])

)

,0)

Bar Chart :  Region as dimension

Measure:

if (sum([Quantity]) <> 0,

if (Sum([DailyQuantitySales]) <> 0,

sum({<[Region] ={"=Sum([margin])>=0"}+{"=Sum([margin])<0"} >}[DailySales]) - sum({<[Region] ={"=Sum([margin])>=0"}+{"=Sum([margin])<0"} >}[DailyVarSales]),

sum({<[Region] ={"=Sum([margin])>=0"}+{"=Sum([margin])<0"} >}[DailySales])

)

,0)

What could be the reason for getting total of KPI is not matching with total of Bar chart

• ###### Re: KPI and Bar Chart Total not matching

Try this for KPI

Sum(Aggr(

If(Sum([Quantity]) <> 0,

If(Sum([DailyQuantitySales]) <> 0,

Sum([DailySales]) - Sum([DailyVarSales]),

Sum([DailySales])), 0)

, Region))

Edit: Also, check here to see why we need this

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/NestedAggregations/sum-of-row…

• ###### Re: KPI and Bar Chart Total not matching

If i use the AGGR then my result will be for that dimension based

but i need a result which is applicable to all the dimension not  only specific to region in KPI .

I can have the Other  Dimention like Country and Customer etc

• ###### Re: KPI and Bar Chart Total not matching

So, each dimension gives a different result, right? How do you expect a dimensionless KPI to know which dimension do you want it to evaluate the if statement on?

• ###### Re: KPI and Bar Chart Total not matching

No my doubt was i am not sure what dimension client can use for selection .

Lets assume user  select  country  dimension value and my KPI is having this expression .

Whether Country Wise KPI value will be equal to Region wise ? Whether This expression will give a correct result

?

Sum(Aggr(

If(Sum([Quantity]) <> 0,

If(Sum([DailyQuantitySales]) <> 0,

Sum([DailySales]) - Sum([DailyVarSales]),

Sum([DailySales])), 0)

, Region))

• ###### Re: KPI and Bar Chart Total not matching

It won't... but that is because the KPI needs to be programmed ahead to give the matching dimension. How do you let the user pick dimension?

• ###### Re: KPI and Bar Chart Total not matching

If you were using this as variable, it could potentially used in a KPI or bar graph measure, where the bar graph has alternate dimensions to slice and dice.

In that case, it *may* work depending on dimensions, as in this case where regions make up countries, then just add the dimensions to the aggr()

I've also used the qs_Variable extension on Branch to allow the user to select the dimension, then you would replace the aggr dimension with your variable value.