topic Re: Use filter in statisticbox with sum() where pretended filter in QlikView

Use filter in statisticbox with sum() where pretended filter

Wed, 18 Jun 2014 07:58:12 GMT

Hi guys,

how can I use many filters to get a sum() ?

My script:

load ID, Name, City, Amount From ...

Now I want to use a statusbox, to sum the amount with specific filter like: amount from Berlin and London; or amount from Dublin,...

with that funktion filter, I want to show the amount, that I really need.

Because I want to show many Amounts without selecting by clicking this to compare them - like to have an overview in my statisticbox with many sums..

Re: Use filter in statisticbox with sum() where pretended filter

MK_QSL — Wed, 18 Jun 2014 08:01:38 GMT

You can use the Resident Load with Where Condition and Group By clauses..

Kindly Update your sample data along with desired out put. will try to help you out.

Re: Use filter in statisticbox with sum() where pretended filter

Wed, 18 Jun 2014 08:33:40 GMT

just like:

load * inline [

ID, Name, City, Amount

1, John Smith, London, 10

2, Harry Gordon, Paris, 20

3, Lisa Müller, Berlin, 30

4, Josh Miller,New York, 40

5, Elisabeth Miller, New York, 50

];

Now I don't want to select / filter by my Script. I want to use the statisticsbox for sum:

1. box "Europe": sum( Amount) where "City" = 'Berlin' and where "City" = 'London' and where "City" = 'Paris'

2. box" USA": sum(Amount) where "City" = 'New York'

Re: Use filter in statisticbox with sum() where pretended filter

MK_QSL — Wed, 18 Jun 2014 08:38:30 GMT

Like this?

Re: Use filter in statisticbox with sum() where pretended filter

Wed, 18 Jun 2014 09:26:58 GMT

Yes right, but what about if I need more then 1 filter/condition like:

Amount from Berlin and New York?

Load City, SUM(Amount) as Europe Resident Temp where City = 'New York' and where City = 'Berlin' Group By City;

didn't work.

Re: Use filter in statisticbox with sum() where pretended filter

Wed, 18 Jun 2014 09:34:58 GMT

What I mean is to use several times the where command for filterung the City and ID

Re: Use filter in statisticbox with sum() where pretended filter

MK_QSL — Wed, 18 Jun 2014 09:36:05 GMT

Try

Load City, SUM(Amount) as BerlingNYAmount Resident Temp

Where City = 'New York' or City = 'Berlin' Group By City;

Re: Re: Use filter in statisticbox with sum() where pretended filter

Wed, 18 Jun 2014 09:58:00 GMT

Thanks much !

But I still got a problem:

If I use:

Load City, SUM(Amount) as USA.Amount Resident Temp where City = 'New York' Group By City;

within using statisticsbox: sum(USA.Amount)

Re: Re: Use filter in statisticbox with sum() where pretended filter

MK_QSL — Wed, 18 Jun 2014 10:03:41 GMT

Share your QVW please

Re: Re: Re: Use filter in statisticbox with sum() where pretended filter

Wed, 18 Jun 2014 10:10:04 GMT

and what about if i wanna select x=ID like

x= >3 and x=<5

Re: Re: Re: Use filter in statisticbox with sum() where pretended filter

MK_QSL — Wed, 18 Jun 2014 10:25:49 GMT

Load SUM(Amount) as TOTALAMT Resident Temp

Where ID >=3 and ID <=5 Group By ID;

Re: Re: Re: Use filter in statisticbox with sum() where pretended filter

Wed, 18 Jun 2014 10:58:14 GMT

CONNECT TO DB1;

Database:
Load "Posting Date",
Month("Posting Date") AS Month,
Year("Posting Date") AS Year,
Amount,
    Description,
    "Document No_",
    "Entry No_",
    "G_L Account No_",
    "Global Dimension 1 Code",
    "Global Dimension 2 Code",

    SUM(Amount) as AmountGlobal where "Global Dimension 1 Code" = 'T83400' or "Global Dimension 1 Code" = 'T83500' or "G_L Account No_" <=479999 and "G_L Account No_" >=400000
;

SQL SELECT *
FROM "OBDC";

Now: I want a sum() of that expression ..

where I have to set the expression ?

1. in the load or

2. after the SQL; as a new table?

T1:
load "Global Dimension 1 Code", "G_L Account No_", SUM(Amount) as AmountGlobal Resident Database where "Global Dimension 1 Code" = 'T83400' or "Global Dimension 1 Code" = 'T83500' or "G_L Account No_" <=479999 and "G_L Account No_" >=400000 Group By "Global Dimension 1 Code";

Re: Re: Re: Use filter in statisticbox with sum() where pretended filter

MK_QSL — Wed, 18 Jun 2014 11:22:55 GMT

In the load side...