Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
matbelli
Not applicable

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
sunny_talwar
Not applicable

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
sunny_talwar
Not applicable

Re: Count elements

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

Somasundaram
Not applicable

Re: Count elements

 

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

Regards,
Somasundaram
matbelli
Not applicable

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
sunny_talwar
Not applicable

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
Not applicable

Re: Count elements

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

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
Not applicable

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