KPI and Bar Chart Total not matching

[prashantsanchet]

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

[sunny_talwar]

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

[prashantsanchet]

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

[sunny_talwar]

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?

[prashantsanchet]

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

[sunny_talwar]

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?

[dwforest]

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.