topic Re: KPI and Bar Chart Total not matching in App Development

KPI and Bar Chart Total not matching

prashantsanchet — Mon, 12 Mar 2018 11:45:47 GMT

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

sunny_talwar — Mon, 12 Mar 2018 11:47:20 GMT

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

prashantsanchet — Mon, 12 Mar 2018 12:39:11 GMT

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

sunny_talwar — Mon, 12 Mar 2018 12:43:06 GMT

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

prashantsanchet — Mon, 12 Mar 2018 12:52:59 GMT

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

sunny_talwar — Mon, 12 Mar 2018 13:01:10 GMT

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

dwforest — Wed, 14 Mar 2018 22:58:50 GMT

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.