Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
matbelli
New Contributor

Count elements

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

IdContoDataImporto ricarica
  € 10.200,00
1024444821/10/2017€ 3.600,00
1024444824/09/2017€ 2.400,00
1024444813/10/2017€ 900,00
1024444807/10/2017€ 900,00
1024444830/09/2017€ 900,00
1024444827/09/2017€ 900,00
1024444801/10/2017€ 500,00
1024444815/10/2017€ 100,00
1024444821/11/2017€ 0,00
1024444820/11/2017€ 0,00
1024444819/11/2017€ 0,00
1024444818/11/2017€ 0,00
1024444817/11/2017€ 0,00
1024444829/10/2017€ 0,00
1024444828/10/2017€ 0,00
1024444825/10/2017€ 0,00
1024444822/10/2017€ 0,00
1024444818/10/2017€ 0,00
1 Solution

Accepted Solutions

Re: Count elements

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

Re: Count elements

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

Somasundaram
Contributor III

Re: Count elements

 

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


-Somasundaram
matbelli
New Contributor

Re: Count elements

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

IdContoDataIdCausaleImporto ricarica
1024444821/10/201727€ 3.600,00
1024444824/09/201727€ 2.400,00
1024444813/10/201727€ 900,00
1024444807/10/201727€ 900,00
1024444830/09/201727€ 900,00
1024444827/09/201727€ 900,00
1024444801/10/201727€ 500,00
1024444815/10/201727€ 100,00
1024444821/11/20175€ 0,00
1024444820/11/20175€ 0,00
1024444819/11/20175€ 0,00
1024444818/11/20175€ 0,00
1024444817/11/20175€ 0,00
1024444829/10/20175€ 0,00
1024444828/10/20175€ 0,00

Re: Count elements


@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
New Contributor

Re: Count elements

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

Re: Count elements

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))
pritamb89
Contributor

Re: Count elements

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

Capture.PNG