Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Sumif not working

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

5 Replies
MVP
MVP

Re: Sumif not working

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

or

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

or using set analysis

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

Not applicable

Re: Sumif not working

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

MVP
MVP

Re: Sumif not working

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.

jerem1234
Valued Contributor II

Re: Sumif not working

Try:

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

Not applicable

Re: Sumif not working

this seems to work....

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

Community Browser