2 Replies Latest reply: Mar 1, 2016 10:39 AM by PAUL YEO RSS

    How to apply VARIABLE $(ColumnDim3) in QS ?

    PAUL YEO

      Hi All

       

       

      I view the QS example , and i found out that it use below expression for plot the chart.

       

       

      Consummer goods KPI :-

      (sum([YTD Sales Amount])-sum([LY YTD Sales Amount])*0.2)/sum([LY YTD Sales Amount])

       

       

      Sales Rep Performance - sales rep variance :-

      if((sum([YTD Sales Amount])-sum([LY YTD Sales Amount]))/sum([LY YTD Sales Amount])>-0.45, (sum([YTD Sales Amount])-sum([LY YTD Sales Amount]))/sum([LY YTD Sales Amount]), 0 )

       

       

      Executive Deskboard - KPI :-

      Sum({<[Fiscal Year]={$(=vYTDYear)}, FiscalMonthNum={"<=$(vCurrentMonthNum)"}>}[Sales Price]*[Sales Quantity])/Sum({<[Fiscal Year]={$(=(vYTDYear)-1)}, FiscalMonthNum={"<=$(vCurrentMonthNum)"}>}[Sales Price]*[Sales Quantity])

       

       

      Executive Deskboard - CY vs LY :-

      Sum({<[Fiscal Year]={$(=(vCurrentYear)-1)}>}[Sales Amount])

       

       

      Performance Deskboard - Sales Variance

      (sum([YTD Sales Amount])-sum([LY YTD Sales Amount]))/sum([LY YTD Sales Amount])

       

       

      Performance Deskboard - Sales comparison :-

      Sum([YTD Sales Amount])

       

       

      Base on above finding , i can conclude that i should not using SET anaylsis when in QS design. instead i should make use of Flag CY or CYTD.

       

       

      money(

      Sum (if($(ColumnDim3), $(ColumnDim51)/$(Columndim89))/1000)

      , $(vMoneyFormatK))

       

       

       

       

      $(ColumnDim3) allow make select between CY or CYTD

       

       

      May i know how can i implement in QS. As QS does not allow me create  variable in setting.

       

       

      Enclsoed my QV sample.

       

       

      Paul