Count elements

[matbelli]

Hi all, how can i count the number of  Importo ricarica that have values bigger than 0? The formula should count 8. thank you

IdConto	Data	Importo ricarica
		€ 10.200,00
10244448	21/10/2017	€ 3.600,00
10244448	24/09/2017	€ 2.400,00
10244448	13/10/2017	€ 900,00
10244448	07/10/2017	€ 900,00
10244448	30/09/2017	€ 900,00
10244448	27/09/2017	€ 900,00
10244448	01/10/2017	€ 500,00
10244448	15/10/2017	€ 100,00
10244448	21/11/2017	€ 0,00
10244448	20/11/2017	€ 0,00
10244448	19/11/2017	€ 0,00
10244448	18/11/2017	€ 0,00
10244448	17/11/2017	€ 0,00
10244448	29/10/2017	€ 0,00
10244448	28/10/2017	€ 0,00
10244448	25/10/2017	€ 0,00
10244448	22/10/2017	€ 0,00
10244448	18/10/2017	€ 0,00

[sunny_talwar]

Is Importo ricarica a field or is it an expression in the table you have shared above?

[Somasundaram]

=Count({<Importo ricarica={">0"}>}Importo ricarica)

[matbelli]

Yeah sorry, it gives me the import only if  i have IdCausale=27.

IdConto	Data	IdCausale	Importo ricarica
10244448	21/10/2017	27	€ 3.600,00
10244448	24/09/2017	27	€ 2.400,00
10244448	13/10/2017	27	€ 900,00
10244448	07/10/2017	27	€ 900,00
10244448	30/09/2017	27	€ 900,00
10244448	27/09/2017	27	€ 900,00
10244448	01/10/2017	27	€ 500,00
10244448	15/10/2017	27	€ 100,00
10244448	21/11/2017	5	€ 0,00
10244448	20/11/2017	5	€ 0,00
10244448	19/11/2017	5	€ 0,00
10244448	18/11/2017	5	€ 0,00
10244448	17/11/2017	5	€ 0,00
10244448	29/10/2017	5	€ 0,00
10244448	28/10/2017	5	€ 0,00

[sunny_talwar]

---

@matbelli wrote:

Yeah sorry, it gives me the import only if  i have IdCausale=27.

---

Not sure I understand your comment? Are you saying that when Import ricarcia >0, you will always have IdCausale = 27? Or are you saying something else?

[matbelli]

this is the expression of Importo ricarica: sum(if(IdCausale=27,Importo))

[sunny_talwar · Accepted solution]

May be try this

Count({<IdCausale = {27}>} Importo)

and see if this works... if not... then may be this

Sum(Aggr(If(Sum({<IdCausale = {27}>} Importo) <> 0, 1, 0), IdConto, Data))

[jerryyang756]

It is because of the currency symbol qlik evaluating it as a string instead as Money format. So I have loaded the data as given below to reformat the data. 

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='€#,##0.00;(€#,##0.00)';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';

Tab1:
LOAD IdConto,
Data,
Money(trim(Replace(Replace([Importo ricarica],'€',''),',',''))) as [Importo ricarica]
FROM
[C:\Users\Qvd.pritam\Desktop\Book1.xlsx]
(ooxml, embedded labels, table is Sheet1);

 After loading I am using below expression :

sum({<[Importo ricarica]={'>0'}>}[Importo ricarica])