Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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
Author

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

jagan
Luminary Alumni
Luminary Alumni

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
Specialist II
Specialist II

Try:

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

Not applicable
Author

this seems to work....

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