Aggr error

[nareshthavidishetty]

Hi,

Below is the logic used in the chart but it gives zero results,

Logic: if(KPI='App Count',num(sum(aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY),CreditApplicationNbrTY,CustomerIDTY)),'#,##0')

But when i it in text object it's working

Text obj Logic: =num(sum(aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY),CreditApplicationNbrTY,CustomerIDTY)))

Please let me know if the logic used in chart is correct.

Thanks...

[sunny_talwar]

What is your chart dimension and what are you trying to get?

[nareshthavidishetty]

Hi,

We have used the inline to get dimension.

Load * INLINE [

KPI, SortOrder

Written Sales, 1

Traffic Volume, 2

App Count, 3

Closing Rate (Traffic), 4

GPM, 5

];

So the chart dimension is KPI

Here instead of the original logic we aggr the CreditApp num by CreditApp num and CustID it gives zero.

Old Logic:

if(KPI='App Count',num(sum(aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY),CreditApplicationNbrTY,CustomerIDTY)),'#,##0')

New logic:

if(KPI='App Count',num(sum(aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY),CreditApplicationNbrTY,CustomerIDTY)),'#,##0')

Chart expression is

,

if(KPI='Written Sales',sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueProductTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueWarrantyTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueDeliveryTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueInstallTY),

if(KPI='Traffic Volume',num(sum({<[OrderDate]={'$(vYesterdayTY)'}>} AdjustedTrafficTY),'#,##0'),

if(KPI='App Count',num(sum(aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY),CreditApplicationNbrTY,CustomerIDTY)),'#,##0'),

if(KPI='Closing Rate (Traffic)',num((sum( {<[OrderDate]={'$(vYesterdayTY)'}>} InvoiceTY)/sum({<[OrderDate]={'$(vYesterdayTY)'}>} AdjustedTrafficTY)),'#.#%'),

if(KPI='GPM',

num((((sum({<[OrderDate]={'$(vYesterdayTY)'}>} ValueProductTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueWarrantyTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueDeliveryTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueInstallTY))-(sum({<[OrderDate]={'$(vYesterdayTY)'}>}CostProductTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}CostWarrantyTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>} CostDeliveryTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}CostInstallTY)))

/(sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueProductTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueWarrantyTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueDeliveryTY)+sum({<[OrderDate]={'$(vYesterdayTY)'}>}ValueInstallTY))),'#.#%'))

)))

)

Thanks...

[nareshthavidishetty]

Hi,

Typo error:

Old logic:

if(KPI='App Count',num(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY),'#,##0')

New logic:

if(KPI='App Count',num(sum(aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY),CreditApplicationNbrTY,CustomerIDTY)),'#,##0')

Thanks..

[sunny_talwar · Accepted solution]

Can you try one of these:

If(KPI='App Count', Num(Sum(TOTAL Aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY), CreditApplicationNbrTY, CustomerIDTY)),'#,##0'))

or

If(KPI='App Count', Num(Sum(Aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY), CreditApplicationNbrTY, CustomerIDTY, KPI)),'#,##0'))

[nareshthavidishetty]

Hi,

Below logic is working,

If(KPI='App Count', Num(Sum(TOTAL Aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY), CreditApplicationNbrTY, CustomerIDTY)),'#,##0'))

Can you please explain why the total differ the result.

Thanks..

[sunny_talwar]

There is a dimension mismatch between your Aggr() function's dimension and the chart dimensions. Adding TOTAL spreads the total across all the value of your KPI Dimension. The best way to see this would be to use this expression

Num(Sum(Aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY), CreditApplicationNbrTY, CustomerIDTY)),'#,##0')

and you will find that this expression is associated with a null dimension (make sure 'Suppress When Value Is Null' is unchecked). By adding TOTAL

Num(Sum(TOTAL Aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY), CreditApplicationNbrTY, CustomerIDTY)),'#,##0')

all KPI dimension value will show the same values and when you use the if statement, you get the value only in front of App Count value within KPI Dimension

If(KPI='App Count', Num(Sum(TOTAL Aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY), CreditApplicationNbrTY, CustomerIDTY)),'#,##0'))

I have a feeling that the second expression will also work, but I cannot say for 100%

If(KPI='App Count', Num(Sum(Aggr(Count(DISTINCT {<[OrderDate]={'$(vYesterdayTY)'}>} CreditApplicationNbrTY), CreditApplicationNbrTY, CustomerIDTY, KPI)),'#,##0'))

[nareshthavidishetty]

Thanks a lot sunny.