2 Replies Latest reply: May 15, 2014 10:14 AM by ARICE01 _ RSS

    Calculating "SubCounts" within Key Values

    ARICE01 _

      I am sure this is not a new challenge, but I can't seem to find a post which reflects my particular problem.  I am trying to figure out how to get Monarch Summary function to calculate a "Sub-Count" within each Unique Key Value (ID_ISWR).  As such, I want to show the Sub-Count incrementing by 1 for each occurance of the Key Value.  I was able to simulate this in Excel using the formula shown below.  Is there a way to do the SubCount calculation in Monarch.

       

           Excel Table: Column Heading begain in Column E and Row 4

                                          

                     SubCount=IF(E5<>E4,1,(IF(E5=E4,H4+1,0)))                    

           ID_ISWR     ID_LOCATION     count     Sub-Count                    

           13821     6008              1     1                    

           13821     6009              1     2                    

           13822     6458              1     1                    

           13822     6461              1     2                    

           13822     6492              1     3                    

           13839     6091               1     1                    

           13839     6096              1     2                    

           13840     6135              1     1                    

           13840     6143               1     2                    

           13840     6144              1     3                    

           13840     6151              1     4                    

           13840     6205              1     5                    

           13840     6518              1     6                    

                                               

                                               

           Excel Pivot Table                                   

           Sum of ID_LOCATION     Sub-Count                              

           ID_ISWR                    1     2     3     4     5     6     Grand Total

           13821                        6008     6009                         12017

           13822                        6458     6461     6492                    19411

           13839                        6091     6096                         12187

           13840                        6135     6143     6144     6151     6205     6518     37296

           Grand Total     24692     24709     12636     6151     6205     6518     80911

        • Calculating &quot;SubCounts&quot; within Key Values
          Olly Bond

          Hello Arice,

           

          It's easy to do this in two passes - export the summary as fixed width text, open this as a text report in a new model, define a page header and then use the Line() function in a calculated field. You're limited to 254 lines per page, which seems to be hard coded into Monarch, so you'd have to be careful if you expected to have sub counts over that limit.

           

          I'm not in front of Monarch now to experiment, but I think you could avoid the 254 line limit by using Rowno(), but again it needs two or three models as Monarch won't let you use an expression based on Rowno() in a summary calculation in the same model.

           

          Best wishes

           

          Olly