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

Date variable to change in Chronological Order - How?

dmohanty — Thu, 11 Jan 2018 22:15:26 GMT

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 -

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.
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!

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

zhadrakas — Fri, 12 Jan 2018 10:26:44 GMT

v_compare_start_date

=AddMonths(v_start_date,-3)

same for other field

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

dmohanty — Fri, 12 Jan 2018 14:03:55 GMT

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.

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

dmohanty — Mon, 15 Jan 2018 16:33:23 GMT

Team,

Any possible suggestions?

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

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

uacg0009 — Tue, 16 Jan 2018 04:55:00 GMT

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

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

zhadrakas — Tue, 16 Jan 2018 09:35:32 GMT

Hello Dicky,

check the qvw. Hope that will help you.

Regard

tim

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

dmohanty — Tue, 16 Jan 2018 16:26:06 GMT

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.

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

dmohanty — Tue, 16 Jan 2018 16:28:16 GMT

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.

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

dmohanty — Tue, 16 Jan 2018 16:50:00 GMT

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.

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

uacg0009 — Wed, 17 Jan 2018 02:42:36 GMT

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