topic Re: Calculated Dimension in Aggr() to Determine Column Max in QlikView

Calculated Dimension in Aggr() to Determine Column Max

richardouellett — Sat, 22 Aug 2015 12:04:24 GMT

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()))))))) ))

Re: Calculated Dimension in Aggr() to Determine Column Max

marcus_sommer — Sun, 23 Aug 2015 18:40:36 GMT

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

Re: Calculated Dimension in Aggr() to Determine Column Max

richardouellett — Sun, 23 Aug 2015 19:42:26 GMT

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.

Re: Calculated Dimension in Aggr() to Determine Column Max

sasiparupudi1 — Sun, 23 Aug 2015 19:55:19 GMT

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

Re: Calculated Dimension in Aggr() to Determine Column Max

richardouellett — Sun, 23 Aug 2015 23:33:53 GMT

Sasi

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

Re: Calculated Dimension in Aggr() to Determine Column Max

marcus_sommer — Mon, 24 Aug 2015 05:14:25 GMT

Have you tried my second suggestion?

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

- Marcus

Re: Calculated Dimension in Aggr() to Determine Column Max

richardouellett — Mon, 24 Aug 2015 15:12:25 GMT

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.

Re: Calculated Dimension in Aggr() to Determine Column Max

richardouellett — Mon, 24 Aug 2015 15:14:17 GMT

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!

Re: Calculated Dimension in Aggr() to Determine Column Max

marcus_sommer — Mon, 24 Aug 2015 15:25:07 GMT

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