Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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'))
What is your chart dimension and what are you trying to get?
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...
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..
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'))
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..
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'))
Thanks a lot sunny.