topic Re: Using a variable value to pull another field value in QlikView

Using a variable value to pull another field value

Tue, 21 Oct 2014 14:52:13 GMT

When I select an date in my calendar I store the value into a variable. I want to use that variable value to pull a field value from my mastercalendar table which will give me the corresponding last year date. Example:

mastercalendar: 'Date' = 7/12/2014 , 'LY Date' = 7/12/2013. In my app, I have a calendar object. I select 7/12/2014 and that value is stored in my variable. I want to use the variable to look in the mastercalendar table to get the LY Date.

Re: Using a variable value to pull another field value

Anonymous — Tue, 21 Oct 2014 15:10:01 GMT

in the expression u can use this to get the LYYear

Only(<Date={'=$(vSelectedDate)'}>LY Date)

Re: Using a variable value to pull another field value

jpapador — Tue, 21 Oct 2014 15:22:34 GMT

In a list box or variable you could do:

=Addmonths(Date, -12)

This will give you the date from last year based on your selected date.

Re: Using a variable value to pull another field value

wms_manis — Tue, 21 Oct 2014 15:48:15 GMT

Here is a way to make it even more dynamic.

I build an island that looks like this:

%Unit	CurrentUnit	LastUnit	PriorUnit
Year	CY	LY	PY
Quarter	CQ	LQ	PQ
Month	CM	LM	PM
Week	CW	LW	PW
Day	CD	LD	PD

Make a list box with %Unit and make sure always one is selected.

Now I can have an expression that compares one period to the next while allowing the user to pick the period to compare:

Use two variables:

vCurPeriodSet is defined as the following:

=only(CurrentUnit)

This returns CY if year is selected or CW if Week is selected.

vLastPeriodSet is defined as the following:

=only(LastUnit)

An example of how it could be used in an expression:

sum({$< $(vCurPeriodSet)={1}>}SumValue) This gives a value for the current period selected.

Sum({$<$(vLastPeriodSet)={1}>}somevalue) returns the value from the prior period.

For the example provided, I used flags in my calendar that were built using the inYear(),inMonth() functions. The calendar will obviously need the all the fields listed in the island you create.