Qlik sense: count expression results is not same with list box same counts

[Vince_CH]

Hi All, i make a KPI with counting the purchase times at 4.87K, i also make a list box besides and counts the quantity to be 4370 instead. it seems to be a gap about 500, what could be the cause?

[sunny_talwar · Accepted solution]

Try to use this for KPI to show the current value in the KPI object

='YTD ' & num(Count(DISTINCT PUR_Price&FiscalYear_order&PUR_Currency&PUR_Currency_1&PUR_Order_Date)/1000,'#,##0.00K;($#,##0.00)')

[Vince_CH]

Hello Stalwar, super, it works. but is it possible to have a comprehensive list box including all duplicated rows？

If it is to sum PUR_Price here, instead of count, how to write the expression differently? thanks!

[sunny_talwar]

Comprehensive list box? Not sure I understand?

[sunny_talwar · Accepted solution]

Not sure, but may you you mean this

Sum(Aggr(
PUR_PRICE
, PUR_Price, FiscalYear_order, PUR_Currency, PUR_Currency_1, PUR_Order_Date))

or

Sum(Aggr(
Sum(DISTINCT PUR_PRICE)
, PUR_Price, FiscalYear_order, PUR_Currency, PUR_Currency_1, PUR_Order_Date))

or

Sum(Aggr(
Avg(PUR_PRICE)
, PUR_Price, FiscalYear_order, PUR_Currency, PUR_Currency_1, PUR_Order_Date))

[Vince_CH]

Sorry for confusion here, actually i intend to express two points here:

1. the list box earlier only show the distinct row without duplicates included, is it possible to have a list contains all the duplicated rows inside for check? just like red circled in below screenshot.

2. to sum PUR_Price, i have used same expression as your first one, but the results are not matched with the list box sum too. The original decimal separator of PUR_Price data is "," and i changed the comma into "." in order for sum calculation. but the sum results from KPI sum expression is not same as the list box PUR_Price sum, what could be the issue?

[sunny_talwar]

1) Load a new field which distinctly define each rows

LOAD ...,
   RecNo() as RowNum
FROM ...;

Now use RowNum as one of the dimensions in your chart

2) 1st expression seemed to have worked for me

[Vince_CH]

i noticed in first one expression, you used PUR_price twice instead I only wrote once, so as:
Sum(Aggr(PUR_Price, PUR_Price, FiscalYear_order, PUR_Currency, PUR_Currency_1, PUR_Order_Date)) in expression, it works, many thanks
:

[sunny_talwar]

Super, I am glad we got what we needed 🙂

[Vince_CH]

Hi Stalwar, very appreciated, by the way, in your earlier list box using Sum(1) to show the duplicated row quantity, what is the expression of that? thanks

[sunny_talwar]

---

@Vince_CH wrote:
Hi Stalwar, very appreciated, by the way, in your earlier list box using Sum(1) to show the duplicated row quantity, what is the expression of that? thanks

---

Sum(1) was the expression 🙂