2 Replies Latest reply: Feb 2, 2017 11:51 AM by Jeremy Brown RSS

    Variable Use Set Expression Result

    Jeremy Brown

      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!