topic Re: Aggr error in QlikView

Aggr error

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggr error

sunny_talwar — Tue, 12 Jul 2016 10:27:13 GMT

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

Re: Aggr error

nareshthavidishetty — Tue, 12 Jul 2016 10:38:00 GMT

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

Re: Aggr error

nareshthavidishetty — Tue, 12 Jul 2016 10:49:25 GMT

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

Re: Aggr error

sunny_talwar — Tue, 12 Jul 2016 10:56:32 GMT

Can you try one of these:

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

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

Re: Aggr error

nareshthavidishetty — Tue, 12 Jul 2016 11:09:30 GMT

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

Re: Aggr error

sunny_talwar — Tue, 12 Jul 2016 12:06:51 GMT

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

Re: Aggr error

nareshthavidishetty — Tue, 12 Jul 2016 12:14:18 GMT

Thanks a lot sunny.