topic Re: Sumif not working in QlikView

Sumif not working

Wed, 26 Jun 2013 14:17:03 GMT

i want to sum the field cost for all entries where the posting date is less than 31/12/2010 and ignore the entries where the posting date is after that date.

for eg, Rs. 100,000 is spent in 2009, 2010, 2011, 2012, then it should show Rs. 2,00,000.

but iam getting value of Zero.

Below is the expression, pls help.

Sum (IF(PD <= 31/12/2010,[Cost Amount (Actual)],0))

Re: Sumif not working

swuehl — Wed, 26 Jun 2013 14:46:09 GMT

If PD is your date field with a date values formatted like 'DD/MM/YYYY', you should check that QV has recognized these values as date values (e.g. by checking the tags in table viewer).

Then you could try:

Sum (IF(PD <= '31/12/2010',[Cost Amount (Actual)] ))

Sum (IF(PD <= makedate(2010,12,31) ,[Cost Amount (Actual)] ))

or using set analysis

Sum ( {<PD = {"<='31/12/2010'"} >} [Cost Amount (Actual)] )

Re: Sumif not working

Wed, 26 Jun 2013 15:30:55 GMT

i am still not getting it.

i get the data for the sum for the entire period only.

i have atatched the file.

pls check

Re: Sumif not working

jagan — Wed, 26 Jun 2013 15:54:58 GMT

Hi,

Try this expression

=Sum ({<PD={'<= $(=MakeDate(2010,12, 31))'}>} [Cost Amount (Actual)])

Also, check whether date format in PD dimension and MakeDate(2010,12, 31) is in same format.

Hope it helps you.

Regards,

Jagan.

Re: Sumif not working

jerem1234 — Wed, 26 Jun 2013 16:50:01 GMT

Try:

=Sum (IF("DATE([Posting Date],'DD/MM/YYYY')" <= '12/31/2010',[Cost Amount (Actual)] ))

Re: Sumif not working

Wed, 26 Jun 2013 18:19:32 GMT

this seems to work....

Sum (IF("DATE([Posting Date],'DD/MM/YYYY')" <= '31/12/2010',[Cost Amount (Actual)] ))