Calculated Dimension in Aggr() to Determine Column Max

[richardouellett]

I have a chart table below that uses a complex calculated dimension (also) below that I have labeled “People”.  I now want to determine the Max of column Freq (= 8 in example below).  In the image below “Max(Total Freq)” does not work.  As a result I believe I need to use the following Formula =Max(Total Aggr(Freq, People)) to achieve the result.  This also does not work since the label “People” as the dimension is not recognized.  I also tried inserting the calculated dimension formula below and this did not work.  Any suggestions/help greatly appreciated.  Thanks

=Max(Total Aggr(Freq,
If( MID(Permut,1,1)='O',OrdID, If( MID(Permut,1,1)='L',ListID, If( MID(Permut,1,1)='G',LabelID, If( MID(Permut,1,1)='C',ColID,
If( MID(Permut,1,1)='A',ArrivaID, If( MID(Permut,1,1)='P',PerfID,If( MID(Permut,1,1)='R',RptID,Null())))))))&'>'&
If( MID(Permut,2,1)='O',OrdID, If( MID(Permut,2,1)='L',ListID, If( MID(Permut,2,1)='G',LabelID, If( MID(Permut,2,1)='C',ColID,
If( MID(Permut,2,1)='A',ArrivaID, If( MID(Permut,2,1)='P',PerfID,If( MID(Permut,2,1)='R',RptID,Null())))))))  ))

[marcus_sommer]

If you have normal dimensions you could use the following approach:

max(total aggr(YourExpression, Dimensions))

I'm not sure if this could be always extend by calculated dimensions - if yes it would be a lot complexer. Easier is therefore to use interrecord-functions, this should work:

rangemax(above(total [Freg], 0, NoOfRows(total)), below(total [Freq], 0, NoOfRows(total)))

- Marcus

[richardouellett]

Marcus

Thanks for your reply.  I have learned since posting this that that you cannot use a calculated dimension in the Aggr() formula; simply does not work.

[sasiparupudi1 · Accepted solution]

Hi

why dont you try putting this formula in the script and create a new filed called people?

If( MID(Permut,1,1)='O',OrdID, If( MID(Permut,1,1)='L',ListID, If( MID(Permut,1,1)='G',LabelID, If( MID(Permut,1,1)='C',ColID,
If( MID(Permut,1,1)='A',ArrivaID, If( MID(Permut,1,1)='P',PerfID,If( MID(Permut,1,1)='R',RptID,Null())))))))&'>'&
If( MID(Permut,2,1)='O',OrdID, If( MID(Permut,2,1)='L',ListID, If( MID(Permut,2,1)='G',LabelID, If( MID(Permut,2,1)='C',ColID,

If( MID(Permut,2,1)='A',ArrivaID, If( MID(Permut,2,1)='P',PerfID,If( MID(Permut,2,1)='R',RptID,Null())))))))  ))

hth

Sasi

[richardouellett]

Sasi

Thank you for this suggestion, I will give it a try and provide feedback shortly.

[marcus_sommer]

Have you tried my second suggestion?

rangemax(above(total [Freg], 0, NoOfRows(total)), below(total [Freq], 0, NoOfRows(total)))

- Marcus

[richardouellett]

Marcus, I did try your suggestion and it did indeed provide the right answer.  However when I trued to sort the by other columns I seemed to have lost functionality.  When I disabled the expression the sorting capabilities returned.

[richardouellett]

Sasi

This approach did indeed work and I was able to resolve original problem with "People" now being a legitimate dimension. Thank you very much!

[marcus_sommer]

Yes you are right some (all) inter-record-functions disable the sorting-settings and apply sorting per load-order (which could be in certain cases adjusted - but in your case there would be not much hope).

- Marcus