Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

Date variable to change in Chronological Order - How?

Hi All,

I have a 2 Variables:  v_start_date and v_end_date, which I am using to enter dates in Calendar.

Also, I have two more variables: v_compare_start_date and v_compare_end_date, which has to change based on changes made in v_start_date and v_end_date.


In Variable Event Trigger, OnChange action has been put for v_start_date and v_end_date, where v_compare_start_date and v_compare_end_date has to change chronologically based on given formula.

Example -

  1. If I enter v_start_date = 10/01/2017 and v_end_date = 12/31/2017, then v_compare_start_date will become 07/01/2017 and v_compare_end_date will become 09/30/2017.
  2. If I enter v_start_date = 12/16/2017 and v_end_date = 01/10/2018, then v_compare_start_date will become 10/16/2017 and v_compare_end_date will become 11/10/2017


Question -

On OnChange variable event trigger, for v_start_date and v_end_date, what Variable values should I put for v_compare_start_date and v_compare_end_date, so to work correctly in Chronological Order, based on examples given above.

Regards!

1 Solution

Accepted Solutions
Partner
Partner

Re: Date variable to change in Chronological Order - How?

Hi,

I was able to get the solution by doing like below.

Based on entered dates for v_start_date and v_end_date, I created a variable,

v_end_start_duration = (Month(v_end_date) - Month(v_start_date)) + 1

The used the variable as a parameter to calculate the

v_start_date_compare = AddMonths(v_start_date , -v_duration)

v_end_date_compare = AddMonths(v_end_date , -v_duration)

In this way, all the Date Months are in Chronological order now.

View solution in original post

9 Replies
zhadrakas
Valued Contributor II

Re: Date variable to change in Chronological Order - How?

v_compare_start_date

=AddMonths(v_start_date,-3)

same for other field

Partner
Partner

Re: Date variable to change in Chronological Order - How?

Hi zhadrakas‌,

Thank you. But this won't work if the Duration between Start Date and End Date is 1 Month.

Example -

v_start_date = 12/01/2017, v_end_date = 12/31/2017

v_compare_start_date should become 11/01/2017 and v_compare_end_date should become 11/30/2017

Based on suggestion provided, above scenario won't work I guess.

Partner
Partner

Re: Date variable to change in Chronological Order - How?

Team,

Any possible suggestions?

I am still trying to hold the values in variables and test.

Partner
Partner

Re: Date variable to change in Chronological Order - How?

Hi Dicky,

Could you please clarify the logic between theses 2 set variables?

because I didn't know why:

If I enter v_start_date = 12/16/2017 and v_end_date = 01/10/2018, then v_compare_start_date will become 10/16/2017 and v_compare_end_date will become 11/10/2017

Thanks

Aiolos

zhadrakas
Valued Contributor II

Re: Date variable to change in Chronological Order - How?

Hello Dicky,

check the qvw. Hope that will help you.

Regard

tim

Partner
Partner

Re: Date variable to change in Chronological Order - How?

Hi zhadrakas‌,

Thank you for the efforts and the qvw.

But still it has a constraint (I guess that might be for a confusion in my scenario). If you select Start Date = 01.10.2017 (Oct 2017) and End Date = 16.01.2018 (Jan 2018), the Compare Dates are coming as 01.09.2017 and 16.12.2017.

It should not be the case. The Compare Dates have to be Chronological. Expected Compare Dates are 01.06.2017 (June 2017) and 16.09.2017 (Sep 2017).

So that the Months cover Chronologically. As the Start Date and End Date cover 3.5 months, the Compare Dates should go back to 3.5 months.

Hope I am clear this time.

Partner
Partner

Re: Date variable to change in Chronological Order - How?

Hi uacg0009‌,

The Compare Date Months and the Entered Date Months have to be Chronological Order.

Start Date and End Date has 2 months - Dec 2017 and Jan 2018, so the Compare Dates should have 2 months in order - Oct 2017 and Nov 2017.

Partner
Partner

Re: Date variable to change in Chronological Order - How?

Hi,

I was able to get the solution by doing like below.

Based on entered dates for v_start_date and v_end_date, I created a variable,

v_end_start_duration = (Month(v_end_date) - Month(v_start_date)) + 1

The used the variable as a parameter to calculate the

v_start_date_compare = AddMonths(v_start_date , -v_duration)

v_end_date_compare = AddMonths(v_end_date , -v_duration)

In this way, all the Date Months are in Chronological order now.

View solution in original post

Partner
Partner

Re: Date variable to change in Chronological Order - How?

Hi Dicky,

Seems like what you want is :

v_start_date

v_end_date

v_compare_start_date = AddMonths(v_start_date,Month(v_end_date) - Month(v_start_date))

v_compare_end_date = v_compare_start_date + v_start_date - v_end_date

But I think, if you want to use them in some expressions, 2 variables is enough.

Thanks.

Aiolos