topic Re: Count elements in QlikView

Count elements

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

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

Re: Count elements

sunny_talwar — Thu, 10 Jan 2019 13:30:24 GMT

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

Re: Count elements

Somasundaram — Thu, 10 Jan 2019 13:35:48 GMT

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

Re: Count elements

matbelli — Thu, 10 Jan 2019 13:40:41 GMT

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

Re: Count elements

sunny_talwar — Thu, 10 Jan 2019 13:43:11 GMT

@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?

Re: Count elements

matbelli — Thu, 10 Jan 2019 13:48:01 GMT

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

Re: Count elements

sunny_talwar — Thu, 10 Jan 2019 13:51:15 GMT

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

Re: Count elements

jerryyang756 — Thu, 10 Jan 2019 13:59:38 GMT

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