topic Re: How to use a parameter in a variable to use an alternate state in set analysis in QlikView

How to use a parameter in a variable to use an alternate state in set analysis

Wed, 25 Nov 2020 16:16:04 GMT

Dear Community,

I have read about alternate states, variables and parameters. But I have diffuculties in the correct use. The following is an example, the real formulas are more complex.

I have defined a variable with a formula that uses set analysis:

set vPossibleTime1=sum(duration)-sum({$<reason={1,2}>}duration);

This formula is used on seperate sheets like $(vPossibleTime1) , but there will be different alternate states in the sheets, e.g: state1 and state2.

The charts on the sheets are set to one of these alternate states in the properties dialogue. When I use the alternates the set analysis does not include the alternate state even if it is set in the properties. So I have to extend the formula:

set vPossibleTime2=sum({[state1]}duration)-sum({[state1]<reason={1,2}>}duration);

So this works now for one sheet, for the other sheets I cannot use it because the alternate state is fixed in the formula.

No I tried to use a parameter in the definition of the variable:

set vPossibleTime3=sum({$1}duration)-sum({$1<reason={1,2}>}duration);

After several trial and errors I found out that I now can use a formula like $(vPossibleTime3('$')) , $(vPossibleTime3('state1')) or $(vPossibleTime3('state2')) in the different sheets.

I expected a formula like $(vPossibleTime3('[state2]')) but this does not work.

Now I want to now if this is the correct syntax:

Defining the variable: set vPossibleTime3=sum({$1}duration)-sum({$1<reason={1,2}>}duration);
using the variable in a formula: $(vPossibleTime3('state1'))

Furthermore I have some nested formulas, i.d. one formula with parameters is used in the other like:

set vAvailableTime1 = $(vPossibleTime1)-sum({$<reason={3,4}>}duration)

together with the Parameters this looks like:

set vAvailableTime2 = $(vPossibleTime3($1))-sum({$1<reason={3,4}>}duration)

With this I can use a formula like $(vAvailableTime2('state2')) in the sheets. In the tests it seems to work.

But before I change the formula in all sheets I want to make sure that this is the correct procedure.

Who can answer this question?

Thanks for your help

Dirk

Re: How to use a parameter in a variable to use an alternate state in set analysis

marcus_sommer — Wed, 02 Nov 2016 17:28:38 GMT

I think your approach should work. I'm not absolutely sure how alternate states will be handled as parameter within variables but I assume the same like by fieldnames and this meant you don't need single-quotes to references them. This meant: $(vPossibleTime3([state2])) and $(vPossibleTime3(state2)) should work.

Beside them I think you could simplify your variable-expression from:

set vPossibleTime1=sum(duration)-sum({$<reason={1,2}>}duration);

set vPossibleTime1=sum({$<reason -= {1,2}>}duration);

Further it might be useful to replace the reason-values of {1,2} or {3,4} also with a parameter whereby the use of a comma-list within a variable-parameter is problematic - but in some cases it worked, see for example: Re: Aggr with Two parameter Variable not working‌.

- Marcus

Re: How to use a parameter in a variable to use an alternate state in set analysis

Mon, 08 May 2017 14:06:46 GMT

The following syntax ist working:

set fTotalDuration=(sum({$1} Duration)

In the diagrams you can use the formular like =$(fTotalDuration('Machine1')).

'Machine1' is the defined alternate state.

Best regards

Dirk