topic Re: $1 in Variable and this Variable in Set Analysis. in QlikView

$1 in Variable and this Variable in Set Analysis.

Tue, 26 Jan 2016 15:01:16 GMT

Hi everyone,

I have a variable, that sets the Number Format, like this

IF(NumFormatName='Decimal' , Num($1, '#.##0,' )

IF(NumFormatName='Thousand.', Num($1 / 1000 , '#.##0,' & ' Tsd.' )

IF(NumFormatName='Million.', Num($1 / 1000000,'#.##0,' &' Mio.')

If(NumFormatName='Billion.', Num($1 / 1000000000,'#.##0,' &' Bio.')

))))

In my table , I have a Expression, which is defined with Set Analysis.

$(vNumFormVar(Sum(Kilometer))) this is no problem. But when I write the expression with Set Analyse, Condition in Set Analysis will be ignored.

This means, this expresseion

= $(vNumFormVar($(=Sum({$+<[StartName]=[EndName],[EndName]=[StartName]>} Kilometer))))

hat same Result as

= $(vNumFormVar(Sum(Kilometer)))

By the way, Sum({$+<[StartName]=[EndName],[EndName]=[StartName]>} Kilometer) is unproblematic.

Thanks!

Re: $1 in Variable and this Variable in Set Analysis.

swuehl — Tue, 26 Jan 2016 23:32:57 GMT

If you are using

= $(vNumFormVar($(=Sum({$+<[StartName]=[EndName],[EndName]=[StartName]>} Kilometer))))

the

$(=Sum({$+<[StartName]=[EndName],[EndName]=[StartName]>} Kilometer))

part will be evaluated once per chart, not per dimension value of your chart.

Would this explain what you are seeing?

Re: $1 in Variable and this Variable in Set Analysis.

Wed, 27 Jan 2016 10:32:55 GMT

I don't understand, what you mean "the part will be evaluated once by chart, not per dimension value of you chart." .

What I saw, = $(vNumFormVar(=Sum({$+<[StartName]=[EndName],[EndName]=[StartName]>} Kilometer))))

hat same result with = $(vNumFormVar(Sum(Kilometer))).

Sum({$+<[StartName]=[EndName],[EndName]=[StartName]>} Kilometer) shows the result in pair. This means, when the StartName is same as EndName, Endname is same as StartName, then shows the results. For example,

From A to B needs 100000 Kilometter, From B to A need 120000 Kilometer. When we only choose A-B ,the Result is:

A - B 100000

B - A 120000.

In pair .

At the same time, Sum(Kilometer) shows the result, only

A- B 100000.

$(VNumFormVar) define the Numberofrmat.

100000.00

100,000.00

100 Tsd.

Re: $1 in Variable and this Variable in Set Analysis.

swuehl — Wed, 27 Jan 2016 10:40:39 GMT

Could you upload a small sample QVW?

Re: $1 in Variable and this Variable in Set Analysis.

Wed, 27 Jan 2016 14:36:46 GMT

Hi everyone,

I have a variable, that sets the Number Format, like this

IF(NumFormatName='Decimal' , Num($1, '#.##0,' )

IF(NumFormatName='Thousand.', Num($1 / 1000 , '#.##0,' & ' Tsd.' )

IF(NumFormatName='Million.', Num($1 / 1000000,'#.##0,' &' Mio.')

If(NumFormatName='Billion.', Num($1 / 1000000000,'#.##0,' &' Bio.')

))))

In my table , I have a Expression, which is defined with Set Analysis.

$(vNumFormVar(Sum(Kilometer))) this is no problem. But when I write the expression with Set Analyse, Condition in Set Analysis will be ignored.

This means, this expresseion

= $(vNumFormVar($(=Sum({$+<[StartName]=[EndName],[EndName]=[StartName]>} Kilometer))))

hat same Result as

= $(vNumFormVar(Sum(Kilometer)))

By the way, Sum({$+<[StartName]=[EndName],[EndName]=[StartName]>} Kilometer) is unproblematic.

Thanks!

Re: $1 in Variable and this Variable in Set Analysis.

swuehl — Wed, 27 Jan 2016 15:20:44 GMT

As I said, the dollar sign expansion

$(=Sum({$+<[StartName]=[EndName],[EndName]=[StartName]>} Kilometer))

is not what you want here. [Or could you explain what you want to do with $(=...) here? ]

If you remove the dollar sign expansion, you are one step further, as now the Sum(...) will be used as parameter, not the evaluated expression.

But now you are running into troubles, because the outer dollar sign expansion of your formatting variable will interpret the comma within the set modifier as separator for its parameter list. Not what you want here.

See:

BI Review: How to write reusable and expandable expressions in QlikView‌

You can add the replace mechanism to your variable definition, then replace the comma in your set modifier with a semi-colon.

You also need to handle the NumFormatName value not being read correctly in all lines of your chart, I think you need also to apply some set analysis to get every number on the same page of your story.

See attached.

Re: $1 in Variable and this Variable in Set Analysis.

Wed, 27 Jan 2016 16:48:13 GMT

Super!

Thank you very much!