topic Re: Start date bigger than end date in QlikView

Start date bigger than end date

absekrafi — Fri, 26 Oct 2018 08:55:18 GMT

Hi Qlikview community,

I added to variables vStartDate and vEndDate to a dimension transaction_date as follow.

vStartDate = Min(Transaction_date)

vEndDate = Max(Transaction_date)

and a trigger for vStartDate:

dimension Field :Transaction_date

='>=' &vStartDate & '<=' &vEndDate

trigger for vEndDate:

dimension Field :Transaction_date

='>=' &vStartDate & '<=' &vEndDate.

I want to get 0 values for all data fields if the user selects vStartDate > vEndDate.

How to implement that in Qlikview?

Thank you in advance,

Best regards,

Abdallah

Re: Start date bigger than end date

Anil_Babu_Samineni — Fri, 26 Oct 2018 09:36:33 GMT

May be this?

If($(vStartDate) > $(vEndDate), Sum(0))

Re: Start date bigger than end date

absekrafi — Fri, 26 Oct 2018 11:09:30 GMT

Hi Anil,

I have in my expression :

sum({<TRANSACTION_DATE= {'$(vStartDate)'}>}STOCKFM).

I changed it to :

if ($(vStartDate) > $(vEndDate), sum(0),sum({<TRANSACTION_DATE= {'$(vStartDate)'}>}STOCKFM))

It didn't work.

I tried also:

if ('$(vStartDate)' > '$(vEndDate)', sum(0),sum({<TRANSACTION_DATE= {'$(vStartDate)'}>}STOCKFM))

But it didn't work also.

Any idea on this?

Thank you,

Re: Start date bigger than end date

Peter_Cammaert — Fri, 26 Oct 2018 11:37:14 GMT

How do you want this to work? Min(FieldName) will never return a value that is larger than Max(FieldName), at most one that is <=...

I guess that vStartDate and vEndDate can be set independently. What values do they contain ?

Re: Start date bigger than end date

absekrafi — Fri, 26 Oct 2018 14:05:14 GMT

Hi Peter,

I added an input box with 2 parameters: I called the first vStartDate and the second vEndDate.

I have transaction_date as dimention field in my Fact table.

The purpose of this is to :

+ select a range for transaction_date.

+ Only data from the Fact table where vStartDate <= transaction_date <= vEndDate can be selected.

What I implemented so far is correct when for example vStartDate = 01/01/2012 and vEndDate = 01/01/2013.

or vStartDate = vEndDate = 01/01/2012.

But if I enter vStartDate = 01/01/2013 and vEndDate = 01/01/2012, I want all the mesures to be null (equal to zero) not different than zero (I didn't yet check the displayed values).

Please the attached file.

Thank you,

Abdallah

Re: Start date bigger than end date

absekrafi — Fri, 26 Oct 2018 14:12:54 GMT

Please see enclosed the straight table I got.

Re: Start date bigger than end date

Peter_Cammaert — Fri, 26 Oct 2018 14:43:36 GMT

You can let QlikView do the heavy lifting, by using Calendar-objects. One fills vStartDate and the other vEndDate. Calendar objects can be set to a default date and their range cn be restricted to a minimum and maximum date value.

For the vStartDate calendar, set

initial value = Min({1}transaction_date)
minimum value = Min({1}transaction_date)
maximum value = vEndDate

For the vEndDate calendar, set

initial value = Max({1}transaction_date)
minimum value = vStartDate
maximum value = Max({1}transaction_date)

That way, you can avoid both variables wandering in forbidden territory

Re: Start date bigger than end date

Anil_Babu_Samineni — Sat, 27 Oct 2018 03:28:47 GMT

Can you test same application like below and show us the result? If 1 returns then zero should work. Is nothing second measure should work.

if ($(vStartDate) > $(vEndDate), 1,0)

Re: Start date bigger than end date

nasirsaikh — Sat, 27 Oct 2018 11:00:06 GMT

If($(vStartDate) > $(vEndDate), yourvalue)

Start date bigger than end date

Re: Start date bigger than end date

swuehl — Sat, 27 Oct 2018 11:05:01 GMT

You can do it like Peter suggested, avoiding MinDate larger MaxDate.

Qlik's numeric search is not always using AND condition for the range, it switches to OR condition in the case where the two limits would return an empty value set. Don't ask me why.

You can get a safe handling in your search pattern using an expression search instead:

='=Transaction_date >=' &vStartDate & ' and Transaction_date <=' &vEndDate

Re: Start date bigger than end date

absekrafi — Mon, 29 Oct 2018 10:11:33 GMT

Hi Anil,

It's working!

Thank you very much.

if ('$(vStartDate)' <= '$(vEndDate)',sum (S_QTEPROD),0)

best regards,

Abdallah

Re: Start date bigger than end date

absekrafi — Fri, 02 Nov 2018 15:09:34 GMT

Hi Peter,

Thank you it's working.

Just I didn't find where to set the initial value.

Best regards,

Abdallah

Re: Start date bigger than end date

Peter_Cammaert — Mon, 05 Nov 2018 08:22:27 GMT

The easiest way to do this is in your script. An initial value is only a placeholder, and usually needs to be assigned only once. For example:

MinMaxTable:

LOAD Min(transaction_date) AS MinDate, Max(transaction_date) AS MaxDate

RESIDENT SomeSourceTransactionTable;

LET vStartDate = Date(Peek('MinDate'));

LET vEndDate = Date(Peek('MaxDate'));

DROP Table MinMaxTable;