Data Missing in Dimensionality()

[tracycrown]

Dear Sir/Madam

Kindly advise why data missing in Total Rows and Columns for Grade ?.

Thank you, Tracy

[rubenmarin]

Hi @tracycrown, If a cell has more than one value it will return null, in this case you have a value for each combination of year, month, id,... so the total row has more than value to show.

One possible solution is to use dimensionality() and secondarydimensionality() to use the appropiate aggreation for the cell to return only one value, ie:

If(SecondaryDimensionality()=0
  ,If(Dimensionality()<=1 
    ,Aggr(If(round(avg(Point),0.1)>= [Lower] and round(avg(Point),0.1) <= [Upper], Grade),ID,Grade)
    ,Aggr(If(round(avg(Point),0.1)>= [Lower] and round(avg(Point),0.1) <= [Upper], Grade),ID,Name,Product,Grade))
  ,If(Dimensionality()<3
    ,Aggr(If(round(avg(Point),0.1)>= [Lower] and round(avg(Point),0.1) <= [Upper], Grade),ID,Year,Month,Grade)
    ,Aggr(distinct If(round(avg(Point),0.1)>= [Lower] and round(avg(Point),0.1) <= [Upper], Grade),Year,Month,ID,Name,Product,Grade)))

[tracycrown]

Dear Mr Rubenmarin

Thank you very much for your kind and quick advice, it works well. I will certainly "Accept as solution" later as you are so knowledgeable and I do not want to miss any opportunity.

Based on past experience, once I accepted as solution, the original query will be closed and you will not be able to read my further queries. Also, I do not know how to write to you using @rubenmarin. 

Not sure whether I have missed out anything after applying your method as it does not work after adding more dimensions such as Operation, Team and Shift , kindly help to review and advise asap.

Wishing you and your family have a Wonderful Merry Christmas.

Thank you, Tracy

[rubenmarin]

Hello @tracycrown, the difficulty here is that you need to calculate the average and assign the grade on different aggretation levels (operation, Team, ID...). To identify the aggregation needed you can use Dimesnionality() and SecondaryDimensionaly().

Dimensionality() returns the dimension number, 0 for horizontal total row (not set in this table, it will be shown if Operations has checked the subtotals options), For each dimesnionality value you will need a different level of aggregation: Operation for Dimensionality()=1, Operation and Team for Dimensionality()=2, etc...

SecondaryDimesnionality() is the same but for vertical dimensions, in this case only 2 options: 0 for the first vertical totals, and 2 for the other columns. So for 0 you don't need to add anything, and for SecondaryDimensionality=2 you need to add Year and Month as aggreation levels. (copy from SecondaryDimensionality()=0 and add the two fields to the Aggr)

Solution attached, I also added Dimensionality() and SecondaryDimensionality() as columns so you can chek it's values.

Also, if you accept a solution the thread is not be closed, I don't know others but I try to answer even if the post is checked as a solution, just rememeber to tag me with @rubenmarin to ensure I'll receive the notification... well... at least I try to answer but some times I'm very busy or I miss the notification.

Merry Christmas!

[tracycrown · Accepted solution]

Dear Rubenmarin

Wow, you are TOO ADVANCED in QV and not easy for me to understand such a long script now, WELL DONE.

I have tagged you few minutes ago and will consult you after studying the script in details.

Thank you very much, Tracy