Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jeremybrown
New Contributor

Variable Use Set Expression Result

In the my "Load Data" script I have the following:

SET vFormatNumber = Num(

  if ($1 > 1000000000000, $1 / 1000000000000,

  if ($1 > 1000000000, $1 / 1000000000,

  if ($1 > 1000000, $1 / 1000000,

  if ($1 > 1000, $1 / 1000, $1))))

, '#,##0.00') &

  if ($1 > 1000000000000, ' T',

  if ($1 > 1000000000, ' B',

  if ($1 > 1000000, ' M',

  if ($1 > 1000, ' K', ''))));

and

let platform_imps = 'Sum({$<gross_flag={0},geo_name=>}gross_imp)';

Note: gross_flag, geo_name, and gross_imp are columns in loaded tables.  The are used here in the set analysis for filtering and for the measure.

In a visualization if I do the following in a measure expression it works:

$(vFormatNumber(47938475345))

However, if I try the following it doesn't work:

$(vFormatNumber($(platform_imps)))

Also the following works:

$(platform_imps)

How do I combine both into the visualization measure expression.  I want a reusable function for number formatting.  I have been forced to do the following:

Num(

  if (Num#($(platform_imps)) >= 1000000000000, Num#($(platform_imps))/1000000000000,

  if (Num#($(platform_imps)) >= 1000000000, Num#($(platform_imps))/1000000000,

  if (Num#($(platform_imps)) >= 1000000, Num#($(platform_imps))/1000000,

  if (Num#($(platform_imps)) >= 1000, Num#($(platform_imps))/1000, Num#($(platform_imps))))))

, '#,##0.00') &

  if (Num#($(platform_imps)) >= 1000000000000, ' T',

  if (Num#($(platform_imps)) >= 1000000000, ' B',

  if (Num#($(platform_imps)) >= 1000000, ' M',

  if (Num#($(platform_imps)) >= 1000, ' K', ''))))

Which is not efficient to use everywhere I need a measure.  Also, I have been forced to surround the variable with Num#() or the results get flaky.  Help please!

1 Solution

Accepted Solutions
MVP
MVP

Re: Variable Use Set Expression Result

I think the outer dollar sign expansions has issues with the comma in the expanded inner dollar sign expansions, which will be interpreted as argument separator.

You can find some workarounds if you search this forum or the net for dollar sign expansion with arguments.

If you need the expanded aggregation only evaluated in total context (e.g. in an gauge chart or text box), you may wanna try

$(vFormatNumber($(=platform_imps)))


or

$(vFormatNumber($(=$(platform_imps))))



edit:

Here are some links that may help coping with the issue

Comma problem (,) workaround for dollar sign expansion with parameters

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

2 Replies
MVP
MVP

Re: Variable Use Set Expression Result

I think the outer dollar sign expansions has issues with the comma in the expanded inner dollar sign expansions, which will be interpreted as argument separator.

You can find some workarounds if you search this forum or the net for dollar sign expansion with arguments.

If you need the expanded aggregation only evaluated in total context (e.g. in an gauge chart or text box), you may wanna try

$(vFormatNumber($(=platform_imps)))


or

$(vFormatNumber($(=$(platform_imps))))



edit:

Here are some links that may help coping with the issue

Comma problem (,) workaround for dollar sign expansion with parameters

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

jeremybrown
New Contributor

Re: Variable Use Set Expression Result

Awesome.  That worked.  I used:  $(vFormatNumber($(=$(platform_imps))))

Community Browser