Variable Use Set Expression Result

[Anonymous]

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!

[swuehl · Accepted solution]

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

[Anonymous]

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