2 Replies Latest reply: Apr 2, 2015 10:01 PM by PAUL YEO RSS

    How to convert SET expression from table to chart ?

    PAUL YEO

      Hi All

       

       

      I have expression :-

       

       

      =(month*30*Avg({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (STK_OPEN+STK_CLOSE)/2))

      /(Sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (COGS_+COGS_INT_)/1))

      +

      (month*30*Avg({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (AR_CLOSE+AR_OPEN)/2))

      /sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (REVENUE+REVENUE_INT_)/1)

       

       

      -

      -1*(month*30*Avg({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (AP_CLOSE+AP_OPEN)/2))

      /sum({$<year = {$(=max(year))}, month = {"<=$(=max({<year={$(=max(year))}>} month))"}>} (COGS_+COGS_INT_))

       

       

      Above expression work fine at Table 1.

       

       

      I don't know how to create a expression for convert the above to table 1 expression to display as chart. So that chart will display :-

       

       

      How to make 1,447 display as 139 , when month = 3

      How to make 1,387 display as 139 , when month = 2

      How to make 1,785 display as 108 , when month = 1

       

       

      Hope some one can advise me.

       

       

      Paul

        • Re: How to convert SET expression from table to chart ?
          Saradhi Balla

          i notice that your dimensions in Table 1 and Chart 1 are different. so the values will not be the same. can you show a screenshot of what your expected chart looks like even if it is a made up chart in ppt?

          • Re: How to convert SET expression from table to chart ?
            PAUL YEO

            Hi Sir

             

            The chart i expected already mention in my question , as below :-

             

            How to make 1,447 display as 139 , when month = 3

            How to make 1,387 display as 139 , when month = 2

            How to make 1,785 display as 108 , when month = 1

             

            The issue i aware is :-

             

            1. YTD Cash Conversion Cycle for Table 1 i display result is wrong , because the average stock i suppose to be from Jan 2015 till Feb 2015 , but now when month= 2 , it only cal Feb value.

             

            In conclusion :-

             

            For SET expression is only capable to use it for sales , as YTD sales for Feb , the SET expression is able to sum up Jan and Feb even when user click on month = 2

             

            For my case is inventory average stock when i click on month = 2 , effectively i only use feb data to cal the average stock , this is not correct !

             

            In another work , if i want to have a field call Target_CCC which is target for Cash conversion cycle , i need to fill in monthly target amount 100 , and use this value to compare with table 1 YTD value , in table it will show it correct , but if i don't click of month =2 only click on year= 2015 , in this case my target will be 100 * 12 = 1200 , which is wrong, because monthly target should be 100 , should not be accumulate.

             

            Hope you understand my issue , as not easy for me explan but i try. 

             

            Finally write till here , i realise that i cannot using SET expression , due to SET expression are only  use for hand sales , for monthly stock in Balance sheet is not same as sales , it cal base jan stock amount sum up Feb stock as total stock for Feb. which is wrong.

             

            Look like i need to spend some time to make my Table 1 value correct first , then can make chart 1 , in order for both correct.

             

            If you happen read till here , and if you have some comment , hope you can share with me.

             

            Paul