4 Replies Latest reply on Jan 19, 2018 5:51 PM by sraposo

    Migrating Excel calculation to AF

    eradwan

      Hi There,

      In the Excel sheet below, the highlighted value at cell C23 gets its value from the above cell if the current time ( in hours as in cell C10)  only if current hour is 01:00 am, but starting from the next hour e.g. 02:00 am in cell D23 the value should be the above cell value in D22 + previous hour value which in this case is C23. This logic continues so E23 = E22 + D23, that is the sum of the above and to the left value. The problem I'm facing is how to get the previous value, I tried prevalue() but it works only on tags, I also tried an analysis to reference the same variable as input and output and got a 'circular reference' error. Any ideas, please?

       

      Emad

        • Re: Migrating Excel calculation to AF
          James Devine

          Hi Emad:

           

          If I understood your situation correctly you are trying to recreate the accumulating total built in your spreadsheet into a PI System Explorer Analysis. From what you describe this can be done.

           

          You may have to configure this to meet your needs, but I just tested and successfully found values using PrevVal('YOUR_ATTRIBUTE_HERE','t'), which would be the previous value for that attribute before midnight today. Then you can use 't+1h', 't+2h', etc to get each hourly value. The roll up would then just be a matter of adding the successive series of values generated as shown here:

           

          hourly.rollup.png

            • Re: Migrating Excel calculation to AF
              eradwan

              Thanks James. Two issues however. I forgot to mention that in the AF version that I want to configure, I'm only interested in the current hour not to report exactly like the Excel sheet and show all hours, and I believe in your version I need to maintain all the hours; is there a simplified way to maintain only the last hour and keep updating it when calculating the new hour? I already have an attribute called 'current hour'. Second issue and most importantly, does 'prevVal()' work with attributes that are not based on a tag?

                • Re: Migrating Excel calculation to AF
                  James Devine

                  Hi Emad:

                   

                  Like anything else there has to be data stored somewhere in order to retrieve it. On that principal I believe you need a pi point to retrieve the data somewhere in the collection of values. With that said the retrieved data does not have to then be stored in another tag in order to use it or view it. In the example above the values retrieved are simply retrieved and added together to appear in unmapped outputs in the analysis tab. They could be mapped to non-pi-point attributes for visibility needs. To answer your first proposition you can easily only show the final resulting value in an attribute or not at all - your choice.

                   

                  This OSISoft YouTube Learning Channel video might help explain the many and powerful ways to manipulate data using PI System Explorer: https://www.youtube.com/watch?v=0iO3Xu0lbTk

              • Re: Migrating Excel calculation to AF
                sraposo

                Hi Emad,

                 

                As James Devine mentions the previous value needs to be stored somewhere. It could be stored in a PI Point, a table (and use a linked table) or you could create a custom data reference. I can't personally think of another way to do a sum of the previous value and the current value in  Asset Analytics.

                 

                Hope this helps,

                Seb