Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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))
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)] )
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
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.
Try:
=Sum (IF("DATE([Posting Date],'DD/MM/YYYY')" <= '12/31/2010',[Cost Amount (Actual)] ))
this seems to work....
Sum (IF("DATE([Posting Date],'DD/MM/YYYY')" <= '31/12/2010',[Cost Amount (Actual)] ))