topic Re: Need to add values before a particular date in QlikView

Need to add values before a particular date

Thu, 09 Jul 2015 04:58:49 GMT

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

Re: Need to add values before a particular date

Thu, 09 Jul 2015 05:43:37 GMT

Hi,

You are trying to do it at script level or front end?

Re: Need to add values before a particular date

aveeeeeee7en — Thu, 09 Jul 2015 05:55:38 GMT

Try this:

=SUM({$<[Posting Date]={"<$(=makedate(2013,03,31))"}>}Amount)

Regards

Av7eN

Re: Need to add values before a particular date

aveeeeeee7en — Thu, 09 Jul 2015 06:07:15 GMT

PFA.

Regards

Av7eN

Re: Need to add values before a particular date

Thu, 09 Jul 2015 07:48:44 GMT

Dear Aveen,

I am not able to open your qvw file as I am novice to Qlikview and using personal edition for same. My version is

Re: Need to add values before a particular date

Thu, 09 Jul 2015 07:50:10 GMT

Hi Reshma,

I am entering it under Expression. Please do suggest.

Regards

Sangeet Malhotra

Re: Need to add values before a particular date

Thu, 09 Jul 2015 07:52:17 GMT

Hi,

Try this

if(num([Posting Date])<=num(date(date#('2013/03/31','YYYY/MM/DD'))),Sum(Quantity))

Re: Need to add values before a particular date

Thu, 09 Jul 2015 08:01:47 GMT

HI Reshma,

Though the expression is correct but results are not desirable. Actually I have a table which includes Store, Posting Date and Quantity. I would like to add quantity before a particular date but not able to do it. I would like to extract stock of a particular store and for a particular brand for a particular month. Please let me know how to handle it and explain the same so that you can help me out.

Regards

Sangeet

Re: Need to add values before a particular date

jagan — Thu, 09 Jul 2015 08:01:55 GMT

Hi,

Try like this

=Sum({<[Posting Date]={"<=$(Date(Date#('2013/03/31', 'YYYY/MM/DD')))}>}Quantity))

What is your Date format of [Posting Date] field?

Regards,

Jagan.

Re: Need to add values before a particular date

Thu, 09 Jul 2015 08:14:00 GMT

Hi Jagan,

Please do have a look.

Re: Need to add values before a particular date

Thu, 09 Jul 2015 08:15:10 GMT

Hi Reshma,

This is not working. I would like to make expression in such a way that it picks data on the basis of Store and brand for a particular month, which I can compare with my Sales Data. I can have a word with you also to explain the same. Please allow.

Regards

Sangeet Malhotra

Re: Need to add values before a particular date

jagan — Thu, 09 Jul 2015 08:16:34 GMT

Try this expression

=Sum({<[Posting Date]={"<=$(Date(Date#('2013/03/31', 'YYYY/MM/DD'), 'DD/MM/YYYY hh:mm:ss TT'))}>}Quantity))

Regards,

Jagan.

Re: Need to add values before a particular date

Thu, 09 Jul 2015 08:20:26 GMT

Hi Jagan,

I am not using variables but static values. How to handle it?

Regards

Sangeet

Re: Need to add values before a particular date

jagan — Thu, 09 Jul 2015 09:21:56 GMT

Hi,

On what basis you have choosen the date 3/31/2013? Based on this we can arrive a dynamic one.

Regards,

Jagan.

Re: Need to add values before a particular date

Thu, 09 Jul 2015 09:34:40 GMT

Hi Jagan,

I have got data in SQL, which i have extracted using View into a separate table. I would like to make expression in such a way that it picks data on the basis of Store and brand for a particular month, which I can compare with my Sales Data. The data is regarding Stock and I need stock for a particular store and for a particular month. I have got following fields

Location Code

Brand Code

Posting Date

Quantity.

I need to add quantity to extract stock details of each month.

Regards

Sangeet Malhotra

Re: Need to add values before a particular date

sasiparupudi1 — Thu, 09 Jul 2015 10:13:13 GMT

Please post your qvw or sample data

Re: Need to add values before a particular date

Thu, 09 Jul 2015 11:56:27 GMT

Dear Sasidhar,

Here is my QVW.

Regards

Sangeet

Re: Need to add values before a particular date

Thu, 09 Jul 2015 11:57:34 GMT

Dear Sasidhar,

I can share my data also if required. Please suggest.

Regards

Sangeet

Re: Need to add values before a particular date

sasiparupudi1 — Thu, 09 Jul 2015 12:24:21 GMT

Please use the following expression

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

Re: Need to add values before a particular date

Thu, 09 Jul 2015 13:29:26 GMT

Dear Sasidhar,

This is also not resolving the issue. My stock on 31/03/2013 was 24 and its showing -15. Kindly help.

Regards

Sangeet