Need to add values before a particular date

[#1]

I would like to add values before a particular date and I am using following formula. Though the expression is OK, but I am still not able to get desired result.

if([Posting Date]<='2013/03/31',Sum(Quantity))

Please help me.

Regards

Sangeet Malhotra

[sasiparupudi1]

May be try

Sum(Aggr(Sum({<[Posting Date]={'<=31/03/2013 12:00:00 AM'}>}Quantity),[Location Code],[Item Category Code]))

[sasiparupudi1]

How are you getting 24? can you please post a screenshot of your comparison may be? What is the objective?

[Kushal_Chawda]

try,

It's working for me. see the attached.

=fabs(sum(aggr(sum({<[Posting Date]={"<=$(=timestamp#('31/03/2013 12:00:00 AM','DD/MM/YYYY h:mm:ss TT'))"}>}Quantity),[Location Code],[Item Category Code],Month)))

[#5]

Dear Kushal,

Thanks for your feedback, but still results are not upto the mark. I am attaching my excel sheet for your reference. Please do have a look as I have extracted this through MS-SQL. I add up all the quantity before a particular date using following SQL query and get desired results. I am mentioning SQL query for your reference.

select sum(Quantity) from View_Stock

where [Location Code]='EAL1' and [Item Category Code]='ARM'

and [Posting Date]<='2013/03/31'

This would give me Qty as 24

select sum(Quantity) from View_Stock

where [Location Code]='EAL1' and [Item Category Code]='ARM'

and [Posting Date]<='2013/04/30'

This would give me QTY as 27.

These answers are correct but I am not able to map them into Qlikview. Please do help.

Regards

Sangeet

[#6]

Dear Sashi,

Thanks for your feedback, but still results are not upto the mark. I am attaching my excel sheet for your reference. Please do have a look as I have extracted this through MS-SQL. I add up all the quantity before a particular date using following SQL query and get desired results. I am mentioning SQL query for your reference.

select sum(Quantity) from View_Stock

where [Location Code]='EAL1' and [Item Category Code]='ARM'

and [Posting Date]<='2013/03/31'

This would give me Qty as 24

select sum(Quantity) from View_Stock

where [Location Code]='EAL1' and [Item Category Code]='ARM'

and [Posting Date]<='2013/04/30'

This would give me QTY as 27.

These answers are correct but I am not able to map them into Qlikview. Please do help.

Regards

Sangeet

[Kushal_Chawda]

Please see the attached.

You can change the Date in input box to see the results dynamically

[#8]

Dear Kush,

Please do have a look at attached screenshot.

EAL1 for ARM for May is showing 24, whereas it should be 24 for April, 27 for May and so on. Please do examine my excel sheet for your reference.

Regards

Sangeet

[#9]

I am not able to open Qlilkview file sent by you as I am on trial version of same. Please do suggest some other option.

Regards

Sangeet

[#10]

Dear Sasi,

Please do help me as I am not able to move further. I would like to add all quantity before a particular date but it would be dynamic. Suppose I start my comparison with FY 2013-14, quantity for April 13 should add all values till 31st March 2013 and if I compare with FY 2014-15, it should add values till 31/03/2014 and so on.

Regards

Sangeet

[sasiparupudi1]

Hi Sangeet,

Please remove month as your dimension and use the expression for 2013-2014

Sum({<[Posting Date]={'<=31/03/2013 12:00:00 AM'}>}Quantity)

Hope this helps