Create a variable based on another variable

[#1]

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

[martinpohl]

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

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

Regards

[#3]

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]

Hi Debbie,

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

Regards,
Cesar

[#5]

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]

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.

[#7]

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

[mike_garcia]

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.