7 Replies Latest reply: Dec 14, 2017 12:59 AM by Yeo Poh sai RSS

    How to make my table display 166K instead of 110K ?

    Yeo Poh sai

      Hi All


      I have a Table. it give the correct value. which i select only 1 SOURCE


      For example SOURC = ID


      But when i want to view the report by all SOURC , it return the wrong value.


      Paul Yeo

      .

        • Re: How to make my table display 166K instead of 110K ?
          Anil Babu

          Perhaps this?

           

          Money(Sum(Aggr(If(Match(SOURCE,'TDSID'),money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),

          Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK))), SOURCE)),$(vMoneyFormatK))

            • Re: How to make my table display 166K instead of 110K ?
              Yeo Poh sai

              wow , you are scripting expert. Thank you very much. it help me a lot.

                • Re: How to make my table display 166K instead of 110K ?
                  Yeo Poh sai

                  Hi Sir


                  I apply your script into my table 1 , it work fine for SOURCE=ID.


                  Now i try to change SOURCE = PG , it does not work.


                  Can you pls help me add another condition ?


                  As i have try it does not work :-

                   

                  Money(Sum(Aggr(If(Match(SOURCE,'TDSID'),money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),

                  Money(Sum(Aggr(If(Match(SOURCE,'TDSPG'),money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),

                  Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK))), SOURCE)),$(vMoneyFormatK))

                    • Re: How to make my table display 166K instead of 110K ?
                      Anil Babu

                      currently, I don't have app in hand. So i can't test. Perhaps this as assumption?

                       

                      Pick(Match(SOURCE, 'TDSID', 'TDSPG')+1,

                      Money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),

                      Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSID'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),

                      Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSPG'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)))

                       

                      I setup the colors on each to better understand For Others    For TDSID     For TDSPG

                        • Re: How to make my table display 166K instead of 110K ?
                          Yeo Poh sai

                          Hi Anil,

                           

                          Wow such a complicated script , you can make it work with out testing . I try to add one more SOURCE = TDSKL , it does not work , where i go wrong ?

                           

                          Pick(Match(SOURCE, 'TDSID', 'TDSPG', 'TDSKL')+1,


                          Money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),


                          Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSID'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),


                          Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSKL'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),


                          Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSPG'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK))))


                            • Re: How to make my table display 166K instead of 110K ?
                              Anil Babu

                              Because, you are varying it to each from different sector to different matching. So, How Pick(Match(...)) functionality works from Left-Right. In your way, you are having different name and you are calculating with different Manner. For a while try this?

                               

                              Pick(Match(SOURCE, 'TDSID', 'TDSPG', 'TDSKL')+1,

                              Money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),

                              Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSID'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),

                              Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSPG'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),

                              Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSKL'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)))