Dynamic Text - Getting max used columns data
Hi all,
As per the attached pic and above mentioned. Is it possible in the dynamic text editor to get the column number of the highlighted section, baring in mind that when changing filters the measures in blue can change and start from 1 instead of 2 or even 4 as per the bottom set of data, which then impacts the actual 'column' number (in grey).
Any help would be greatly appreciated.
Thanks,
Dan
9 replies
-
Hi Dan,
Can you be a little more precise in how the specific column is selected? Must all the rows be populated with values? Is it the column with highest total of values? Or both? Or any other selection criteria.
Thanks,
Ian
-
Hi Ian,
Thanks for getting back to me. I'm hoping the column can be selected via the pql formula in the dynamic text grid section. I would want it to always get the last column with data in from the 'singular core ref' part as per the pic or atleast get that column number so that I can use it when using the amount from that and dividing it by the % from the benchmark section above. Max will not work as that figure doesn't incrementally increase as the months progress, it could see a reduction.
Does that help at all?
Thanks,
Dan
-
Hi Dan,
Just so I am sure, you want the column number that contains the last occurrence across the row of a value for both AmountCAD AND CAD Developed % Latest.
Is it possible in the data that there will be gaps in the data for Single Core Ref values? I.e. in the data above, column 10 is null but there are values for column 11? Which one would you want in that scenario?
Ian
-
Hi Dan,
Here's what I've come up with.
Consider this grid:
As per your example, I want to find the number of the column that contains the last non null value for a specific row moving form left to right. So for Japan, say, the returned value would be 10 (Columns numbered from 0).
The approach I've taken is to find the row number that contains the text required, then traverse the columns from right to left for that row, returning the column number for any non null cell. This returns a string of numbers, the first number returned will be the highest number column containing a value.
Here's the Dynamic Text code I wrote to achieve this:
Left(
Loop(If(IfNull(Data(FindRowNumber("Japan","true"),NumberOfColumns()-index-1),"X")<>"X",NumberOfColumns()-index-1+",",""),NumberOfColumns()),
Indexof(Loop(If(IfNull(Data(FindRowNumber("Japan","true"),NumberOfColumns()-index-1),"X")<>"X",NumberOfColumns()-index-1+",",""),NumberOfColumns()),","))FindRowNumber("Japan","true") gives the row number we are examining
Data(FindRowNumber("Japan","true"),NumberOfColumns()-index-1) will return the value of each cell as the Loop function increments the index value. NumberOfColumns()-index-1 gives the column reference number for the Data() function.
IfNull(Data(FindRowNumber("Japan","true"),NumberOfColumns()-index-1),"X") examines each of the cell values and if null returns "X" otherwise the cell value.
If(IfNull(Data(FindRowNumber("Japan","true"),NumberOfColumns()-index-1),"X")<>"X",NumberOfColumns()-index-1+",","") looks at each value returned by IfNull and if is not null (<>"X"), returns the column reference number concatenated with a ",", otherwise returns nothing.
Loop(If(IfNull(Data(FindRowNumber("Japan","true"),NumberOfColumns()-index-1),"X")<>"X",NumberOfColumns()-index-1+",",""),NumberOfColumns()) Specifies how many times to iterate through the functions.
Finally,
Left(
Loop(If(IfNull(Data(FindRowNumber("Japan","true"),NumberOfColumns()-index-1),"X")<>"X",NumberOfColumns()-index-1+",",""),NumberOfColumns()),
Indexof(Loop(If(IfNull(Data(FindRowNumber("Japan","true"),NumberOfColumns()-index-1),"X")<>"X",NumberOfColumns()-index-1+",",""),NumberOfColumns()),","))finds the position of the first "," in the string and returns the characters prior to that, i.e. the highest column number.
Putting this into a Present slide we can slice the grid and see how the results vary:
Hope that helps.Ian
-
Thanks very much Ian, I was just about to reply to your previous message. I will take a look at this and let you know how it goes. Many thanks
-
Hi Ian,
I have incorporated this into my dynamic text grid, and it comes back with the column header number that I want to use to drive the rest of the calculation, however, when wrapping a data function around it to give it a row number and then the column number via your method it doesn't work and i get a PQL validation error. Can I not use the data function with the formula you have posted?
Many thanks,
Dan
-
Hi Dan,
The expression I provided returns a string. Use ConvertToNumber() to change it into a numeric and it will then work.
BTW, I would be interested in what your final calculation is. IF you are calculating between two values in your grid, it may be easier and simpler as a Semantic Calculation in your query, rather than a convoluted calculation in Dynamic Text.
Ian
-
Hi Ian, Many thanks, now working as intended (I need to test it on multiple examples but seems all good). See pic for full calculation. Do you think it would be better doing this another way then? -
Hi Dan,
Create a new member in your [Core Reference] Dimension, like [My Calc] such that it equals:
([Core Reference].[Singular Core Ref - Latest YOA],[Measures].[Amount CAD])/
([Core Reference].[Benchmark Grouping - All Years],[Measures].[CAD Developed % Latest])The above is the native PQL, but you can use the graphical Formulate editor to quickly create the calculation without writing code.
This will give you a new Line item, My Calc, with the columns the ratio of ([Core Reference].[Singular Core Ref - Latest YOA],[Measures].[Amount CAD]) to ([Core Reference].[Benchmark Grouping - All Years],[Measures].[CAD Developed % Latest]) for each Column.
You can then just read off the last one, or use the above technique to just pick off the number you want if it is required to be embedded in some text, without having to do the division.
Hope that helps.
Ian