Setting a variable to select an item from a field

Tue, 02 Feb 2016 17:06:01 GMT

I am fairly new to QlikView and do not have much experience with Variables so any help is welcome.

In my QlikView report is a field which outlines the scenario i.e. Budget, Actual, Actual 15 for the data.

Unfortunately data that relates to last year is pulling in as scenario 'Actual 15'.

I am having to complete set analysis to pull the data that I need and I would prefer to use a variable for 'LastYear' that I can set to 'Actual 15' but can amend next year to 'Actual 16'. Otherwise I will need to amend all of the formulas that I have written (about thirty!) when the year rolls over.

I have set the following variable but do not think that it is right.

vLastYear Only(Scenario)='Actual 15'

The following formula works for 'Actual 15'

//IF Scenario is Actual 15
((if(Only(Scenario)='Actual 15',
//THEN select Actual but with the NominalPeriodYear as selection but plus 1 (the year will be showing as 2015)
sum({$<NominalPeriodYear={$(=Only(NominalPeriodYear)+1)},Scenario={Actual}>}Value),
//ELSE Sum the value for Actual and the year selected 2016
Sum({<Scenario={Actual}>}Value))))

If the variable is set correctly then how do I get it to be recognised in the formula. I have tried ((if(Only(Scenario)=$(vLastYear).

Thanks

Re: Setting a variable to select an item from a field

sunny_talwar — Tue, 02 Feb 2016 17:21:50 GMT

Try without dollar sign expansion:

=If(Only(Scenario) = vLastYear, ....)

topic Re: Setting a variable to select an item from a field in QlikView

Setting a variable to select an item from a field

Re: Setting a variable to select an item from a field