Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

$1 in Variable and this Variable in Set Analysis.

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!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

6 Replies
swuehl
MVP
MVP

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?

Not applicable
Author

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
MVP
MVP

Could you upload a small sample QVW?

Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

Super!

Thank you very much!