4 Replies Latest reply: May 15, 2013 12:33 PM by Wes Heilman RSS

    Calculate net change in value from last period using set analysis

      Hi,

      I have a simple chart that plots a dollar amount against months as they proceed. Thus the X-Axis has Months (BankReport.BR.TransMonth) and Y-Axis Shows the dollar amount (sum(BankReport.BR.Payment). Now I want a curve running along showing net change every period from the last, and I am trying to use set analysis for the same.

      To get previous periods dollar amount I write:

      sum(<BankReport.BR.TransMonth = {$(#=Only(BankReport.BR.TransMonth)-1)}>} BankReport.BR.Payment)

      and to get net change every period, I write:

      (sum(BankReport.BR.Payment) - sum(<BankReport.BR.TransMonth = {$(#=Only(BankReport.BR.TransMonth)-1)}>} BankReport.BR.Payment)

      However neither the "Previous period Dollar amount" nor the "Net Change" show any value. It would be great if someone could guide as to how I can get these values to show. I am using these in the Chart Expressions editor for creating bar graphs.

      Two related questions:

      Can I use "Count" in a set analysis expression, as all examples I have seen online use "Sum" aggregation only. Also if I use more than one dimensions, can I use trellis along with set analysis.

      Sohel

       

       

       

       

        • Calculate net change in value from last period using set analysis

          Hi,

          not sure about the trellis but I can answer your first q

          you can use count in set analysis eg:

          count({<YMDate = DisplayYM, Auction_Direct_Flag = {'Direct Sales'}>}distinct Stock_Number)

          S

            • Calculate net change in value from last period using set analysis

              Hi,

               

              Thanks for the Reply. I checked "Count" working in Set Analysis. I am however still waiting for way to plot a net change graph month on month for the $Value changes occuring every month.

              Request members to respond.

              Rgd

               

              Sohel

               

               

                • Re: Calculate net change in value from last period using set analysis

                  I see this is an old thread, however, I have been able to capture net change by using the 'Peek' command which enables my application to find changes in specified fields. It is important to sort the records in a meaningful order before using 'Peek' in order to obtain the right results.  My challenge at this point is to use set analysis on the results. I am also still looking for a way to 'Peek' through a list of feilds and talley the results rather than scripting a 'Peek' command one field at a time.  Here is a line that looks for Extended Price Changes on PO lines:

                   

                   

                  If(Peek('%%OrderLineKey') = %%OrderLineKey , If(Match(Peek('PDAEXP'), PDAEXP) = 0, 1, 0), 0) as

                  CheckExtPrice

                  • Re: Calculate net change in value from last period using set analysis

                    I see this is an old thread, however, I have been able to capture net change by using the 'Peek' command which enables my application to find changes in specified fields. It is important to sort the records in a meaningful order before using 'Peek' in order to obtain the right results.  My challenge at this point is to use set analysis on the results. I am also still looking for a way to 'Peek' through a list of feilds and talley the results rather than scripting a 'Peek' command one field at a time.  Here is a line that looks for Extended Price Changes on PO lines:

                     

                     

                    If(Peek('%%OrderLineKey') = %%OrderLineKey , If(Match(Peek('PDAEXP'), PDAEXP) = 0, 1, 0), 0) as

                    CheckExtPrice