$1 in Variable and this Variable in Set Analysis.

[#1]

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!

[swuehl]

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?

[#3]

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.

[swuehl]

Could you upload a small sample QVW?

[#5]

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!

[swuehl · Accepted solution]

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.

[#7]

Super!

Thank you very much!