Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

2 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

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

to

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

Not applicable
Author

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