Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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))
Is Importo ricarica a field or is it an expression in the table you have shared above?
=Count({<Importo ricarica={">0"}>}Importo ricarica)
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 |
@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?
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))
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])