Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
See why IDC MarketScape names Qlik a 2025 Leader! Read more
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Create a variable based on another variable

I have a variable vMaxRMDate that creates a date:

=if(max({1< [Mart]={Revenue}>}[Date])=Today(0), max({1<[Mart]={Revenue}>}[Date])-1,max({1<[Mart]={Revenue}>}[Date]))I

Then based on this date, i want another variable to calcualte the Work Day associated with the above date so I tried this and nothing:

=sum({<[Date]={'$(vMaxRMDate)'}>}[Work Day in FQ])

the first value is workign and getting me 5/18/2011

the second variable should give me 13 because the Date 5/18/2011 equates to work day 13 stored in the calendar table.

What other formula can I use to do this. I have tried various IF statemsn adn nothing

7 Replies
martinpohl
Partner - Master
Partner - Master

What is happening if you change the variable to 5/18/2011?

Maybe the ' in the {'$(vMax...)'} statement are too much.

Regards

Not applicable
Author

If [Date] is a DateTime, QlikView is really Picky on what he considers a valid selection.

Try this:

  • Create a listbox with Date Values
  • Open the search box on the listbox
  • Write a valid date until a value is selected

You probably will have to input a complete dateTIME, according to the format specified on the first page of the load script.

E.g., something like 5/18/2011 00:00:00

So, the variable should be =sum({<[Date]={'$(=Date(vMaxRMDate, 'dd/MM/yyyy hh:mm:sss'))'}>}[Work Day in FQ])

Use the date format you have on your load script.

cesaraccardi
Specialist
Specialist

Hi Debbie,

Have you tried this way: =sum({<[Date]={'=$(vMaxRMDate)'}>}[Work Day in FQ]) ?

Regards,
Cesar

Not applicable
Author

thank you all, so far nothing has worked.  I got closer with Cesar's answer but I got the sum of all days, so i changed to MAX and now I get 66 (maximum days in quarter) instead of 13 which is the specific work date for 5/18/2011

=max({<[Date]={'=$(vMaxRMDate)'}>}[Work Day in FQ])

nagaiank
Specialist III
Specialist III

I am not sure if I understood what you are trying to do and why.

If you are able to get vMaxRMDate, and if Date and [Work Day in FQ] are fields in your calendar file, you may try to use the 'lookup' expression for get the desired value.

Not applicable
Author

This is not a feature I have used before.  How do I use "lookup"

mike_garcia
Specialist
Specialist

A few things you should try (one of them has to work):

  1. Change the single quotes to double quotes: =sum({<[Date]={"$(vMaxRMDate)"}>}[Work Day in FQ])
  2. Add (and not replace) Double quotes, in case qlikvew is interpreting the variable as a literal. =sum({<[Date]={"'$(vMaxRMDate)'"}>}[Work Day in FQ])
  3. The final one, this one will work for sure, but is a little messier. Force qlikview to interpret the literal value in your variable as a Date: =sum({<[Date]={"$(=Date#('$(vMaxRMDate)', 'MM/DD/YYYY'))"}>}[Work Day in FQ])

Make sure to type the correct format in the 3rd option. Also, make sure your default date format matches the one that the [Date] field is using, otherwise, specify the format.

If you go for the 3rd option, it would be a good idea to check what the expression is returning, using a Text Object.

Hope it helps.

Mike.

Miguel García
Qlik Expert, Author and Trainer