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

Calculating &quot;SubCounts&quot; within Key Values

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

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

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

Thanks Olly... This works flawlessly. I wish I could avoid the 2-pass, but the extra step gets me where I want to be.

Thanks again and the Monarch team spirit is thriving!!!