topic Create a variable based on another variable in QlikView

Create a variable based on another variable

Thu, 19 May 2011 15:31:08 GMT

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

Create a variable based on another variable

martinpohl — Thu, 19 May 2011 15:48:07 GMT

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

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

Regards

Create a variable based on another variable

Thu, 19 May 2011 16:03:02 GMT

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.

Create a variable based on another variable

cesaraccardi — Thu, 19 May 2011 20:11:01 GMT

Hi Debbie,

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

Regards,
Cesar

Create a variable based on another variable

Thu, 19 May 2011 20:16:14 GMT

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])

Create a variable based on another variable

nagaiank — Thu, 19 May 2011 22:15:43 GMT

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.

Create a variable based on another variable

Thu, 19 May 2011 22:20:35 GMT

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

Create a variable based on another variable

mike_garcia — Thu, 19 May 2011 22:34:42 GMT

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

Change the single quotes to double quotes: =sum({<[Date]={"$(vMaxRMDate)"}>}[Work Day in FQ])
Add (and not replace) Double quotes, in case qlikvew is interpreting the variable as a literal. =sum({<[Date]={"'$(vMaxRMDate)'"}>}[Work Day in FQ])
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.