topic Re: Re: Date Function in Expression in QlikView

Date Function in Expression

Mon, 31 Mar 2014 09:48:18 GMT

Hi All,

i need help in getting sun function flexible. currently i have 3 columns:

ID Date Qty

1 28/3/2014 100

1 27/3/2014 1000

1 24/3/2014 2000

in expression i use below and i get sum value for that date:

Sum({Date = {'$(=Tdate)'}>}Qty gives be 100 ........ Assume variable Tdate = 28/3/2014

Now i want in same expression and get value of 1100 from Date 28/3/2014 & 27/3/2014

please advise what needs to be changed

thanks

Re: Date Function in Expression

Yousef_Amarneh — Mon, 31 Mar 2014 09:51:06 GMT

Try this

Sum({Date = {'$(=Tdate)','$(=Tdate-1)'}>}Qty)

Re: Date Function in Expression

Mon, 31 Mar 2014 10:03:34 GMT

Thanks Yousef...

But the above was a sample data.... Date column have 1 year data.. thus your suggestion may not work..... i want to use

sum({Date > {'$(=Tdate)'}>}Qty) but it is not working ...guess the syntax is wrong

please advise

thanks

Re: Date Function in Expression

Yousef_Amarneh — Mon, 31 Mar 2014 10:09:12 GMT

Could you please give me more details, I cannot get you very well. what do you mean by flexible function?

Re: Date Function in Expression

its_anandrjs — Mon, 31 Mar 2014 10:11:26 GMT

Check your date format for field Date, it is in date format or in string.

Hope this helps

Thanks & Regards

Re: Date Function in Expression

Mon, 31 Mar 2014 10:16:32 GMT

ok so i have 1 year data in this table and in expression i want to sum Qty where Date is greater than or equal to 1 week.

1 Week is a variable and i can display this variable in input box and ask user to change it as per its convenience

@ Anand - i checked the format of the variable and Date column is same... the Expression is i feel is incorrect

please advise

Re: Date Function in Expression

Yousef_Amarneh — Mon, 31 Mar 2014 10:29:49 GMT

Create 2 variables, vFromDate and vToDate then use the following expression

sum({<Date= {"<=$(=vFromDate)>=$(=vToDate)"}> } Qty)

Re: Re: Date Function in Expression

Mon, 31 Mar 2014 10:49:28 GMT

thanks Yousef, but the desired result is not coming

i defined the below variables:

vToDate - today's value

vFromDate - 24/03/2014

the table gives me sum of Qty for whole 1 year

please advise

Re: Date Function in Expression

Mon, 31 Mar 2014 10:50:52 GMT

Try this:

Sum({Date = {">=$(=Tdate)"}>}Qty)

Re: Date Function in Expression

MayilVahanan — Mon, 31 Mar 2014 10:52:41 GMT

Try like this

vStartDate = 22/3/2014

vEndDate = 27/3/2014

=Sum({<Date = {">= $(=vStartDate) <= $(=vEndDate)"}>} Qty)

i checked the format of the variable and Date column is same...

So there is no need to apply date format, its will give correct answer.

Re: Re: Date Function in Expression

Mon, 31 Mar 2014 11:03:00 GMT

thanks