Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Start date bigger than end date

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

1 Solution

Accepted Solutions
Highlighted

Re: Start date bigger than end date

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

12 Replies
Highlighted

Re: Start date bigger than end date

May be this?

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

Highlighted
Creator III
Creator III

Re: Start date bigger than end date

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,

Highlighted

Re: Start date bigger than end date

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 ?

Highlighted
Creator III
Creator III

Re: Start date bigger than end date

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

Highlighted
Creator III
Creator III

Re: Start date bigger than end date

Please see enclosed the straight table I got.

Highlighted

Re: Start date bigger than end date

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

Highlighted

Re: Start date bigger than end date

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)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

Re: Start date bigger than end date

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

Highlighted
MVP
MVP

Re: Start date bigger than end date

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