Start date bigger than end date

[absekrafi]

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

[Anil_Babu_Samineni · Accepted solution]

May be this?

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

[absekrafi]

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,

[Peter_Cammaert]

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 ?

[absekrafi]

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

[absekrafi]

Please see enclosed the straight table I got.

[Peter_Cammaert]

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

[Anil_Babu_Samineni]

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)

[nasirsaikh]

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

• Start date bigger than end date

[swuehl]

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