topic Re: how to write an expression for date range using variable in QlikView

how to write an expression for date range using variable

sakshikaul — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I want to write an expression using variables start_date and end_date as shown in image

I want to show sales between the start_date and end_date. Both start_date and end_date are variables and these are text boxes

Now want to call both the variables in chart by writing expression using set analysis, Please help me in writing the same.

please help stalwar1‌

Re: how to write an expression for date range using variable

rubenmarin — Fri, 02 Nov 2018 10:10:20 GMT

Hi Sakshi, it can be something like:

Sum({<Start_Date={">=$(vLastYearDate)<=$(vCurrentYearDate)"}>} MetricField)

{<...>} -> Define set analysis

Start_Date= -> Field to filter

{"..."} -> Conditions to filter

$() -> Gets the variable value

Re: how to write an expression for date range using variable

sakshikaul — Fri, 02 Nov 2018 10:36:54 GMT

This expression is working but in my report I have following requirement

I have an internal table maintained in the qlikview

InlinetableLastYear:

LOAD

Date(Date#(InlineDate, 'DD/MM/YYYY'), 'D/MM/YYYY') as lastDate,

event

INLINE [

InlineDate,event

10/10/2018,Diwali

];

Now as shown in an image below , when the user will select comparison no. of days as 5

the date will be selected for which the user Is willing to see the sales

last_date=10 oct 2018 - comparison no of days =5

so final result is 5/10/2018

so here 5/10/2018 will become as start_date taken in variable in text box and end_date will be 10/10/2018

so when I am changing the value of no of comparison days from 5 to 3 the date is changing in text box as seen in image but the result in chart is not being reflected so what changes are required in expression to achieve the same ?

please help as soon as possible

Re: how to write an expression for date range using variable

rubenmarin — Fri, 02 Nov 2018 10:47:09 GMT

Following last example vCurrentYearDate can be "=lastDate"

And vLastYearDate can be "=Date('$(vCurrentYearDate)'-$(vComparisonDays)"

Sum({<Start_Date={">=$(vLastYearDate)<=$(vCurrentYearDate)"}>} MetricField)

Re: how to write an expression for date range using variable

sakshikaul — Fri, 02 Nov 2018 11:08:40 GMT

so you want me to do changes here ?

=Sum({<Sold_Date={">=$("=Date('$(vCurrentYearDate)'-$(vComparisonDays)")<=$("=lastDate)"}>} [inv_value])

so you want me to do the changes here?

please check the variable declaration list here

Re: how to write an expression for date range using variable

rubenmarin — Fri, 02 Nov 2018 11:14:15 GMT

can you upload a sample copy of the document?

In file -> Reduce data you can make a smaller document.

In Document properties -> Cipher you can scramble field values

Re: how to write an expression for date range using variable

sakshikaul — Fri, 02 Nov 2018 12:01:18 GMT

now from where I can get attach option to attach a doucument?

Re: how to write an expression for date range using variable

rubenmarin — Fri, 02 Nov 2018 12:27:25 GMT

Use advanced editor.

Below the body you have the option to 'attach'

Re: how to write an expression for date range using variable

sakshikaul — Mon, 05 Nov 2018 03:27:26 GMT

can you please send me the link from where I will get this advanced editor page ?

Re: how to write an expression for date range using variable

sakshikaul — Mon, 05 Nov 2018 03:58:12 GMT

Why I am not able to attach this document ?

Re: how to write an expression for date range using variable

sakshikaul — Mon, 05 Nov 2018 06:50:40 GMT

qvw attachement

Re: how to write an expression for date range using variable

sakshikaul — Tue, 06 Nov 2018 04:16:06 GMT

Please help stalwar1‌, rubenmarin‌ loveisfail‌

Re: how to write an expression for date range using variable

Anil_Babu_Samineni — Tue, 06 Nov 2018 04:30:07 GMT

Sure - Can you tell us Which sheet we need to look and explain little more, Which filters are happened and what you want to calculate?

Re: how to write an expression for date range using variable

sakshikaul — Tue, 06 Nov 2018 04:46:25 GMT

Hi,

I want to write an expression using variables start_date and end_date as shown in image

I want to show sales between the start_date and end_date. Both start_date and end_date are variables and these are text boxes

Now want to call both the variables in chart by writing expression using set analysis.

I want when user will select an event and will select a corresponding even date (lastDate)

now if user wants to know the sales on pre event date ie sales before 10 days with respect to selected date

and after once we get the date in text box that date will become starting date and last date will be as an event date

so I need to write an expression for start date and end date in pivot chart. How to write an expression?

Re: how to write an expression for date range using variable

rubenmarin — Tue, 06 Nov 2018 07:38:57 GMT

Hi Sakshi, there is no data to test in this sample. In reduce data you can select keep possible values to at least have some data to play.

Anyway, the answer should be closer to:

Sum({<Start_Date={">=$(vLastYearDate)<=$(vCurrentYearDate)"}>} MetricField)

vLastYearDate is a variable with value: =Date('$(vCurrentYearDate)'-$(vComparisonDays))

vCurrentYearDate is a variable with value: =Date(lastDate)