calculated dimension to show on one row

[dmxmikey]

I need to be able to get my data referring to the same product code on same row, currently each canlculated dimension is on a separate row as per attached screenshot

[JonnyPoole]

Your data model seems to have 5 unique  records for these dimensions  (assuming only QTY is an expression).

It would be great to address this in the data model but if you can't you should be able to use  concat( total <Dimension1,Dimension2...>  )  to get all the records within a distinct grouping of select dimensions and apply your condition over that for the calculated dimensions to figure out if 'Contains Eggs'=Y etc...

once the entire row is the same , the chart should collapse that for us.

Posting the QVW will help in this case , there are many unknowns and i can only write in broad terms.

[jpenuliar]

Hi,

your attached screenshots shows your data presented with a Pivot Table Chart object.

You could change that to a Straight Table Chart Obj

[dmxmikey]

attached is my qvw

[dmxmikey]

Attached is my qvw

<http://www.thomasridley.co.uk/>

Mike Dube ACCA

Business Process Manager

T: 01359 270536

Favourite Dish: Lamb Stew and Dumplings<http://www.countryrange.co.uk/recipes/?ID=7> Follow us on Twitter<https://www.twitter.com/ThomasRidleyFS> <https://www.twitter.com/ThomasRidleyFS>

Delivering the best in Foodservice...

[JonnyPoole]

thanks very much.  i noticed its stripped of data . can you share a subset or a representative sample ?

[dmxmikey]

Is this any better

<http://www.thomasridley.co.uk/>

Mike Dube ACCA

Business Process Manager

T: 01359 270536

Favourite Dish: Lamb Stew and Dumplings<http://www.countryrange.co.uk/recipes/?ID=7> Follow us on Twitter<https://www.twitter.com/ThomasRidleyFS> <https://www.twitter.com/ThomasRidleyFS>

Delivering the best in Foodservice...

[JonnyPoole · Accepted solution]

It does.

So i replaced your existing calculated dimensions like this one:

if([ALL.alg-code]='ORG',if([ALL.alg-stat]='N','N',if([ALL.alg-stat]='Y','Y',)),null()),'-')

With an aggregate that would spit out 1 row per non-null value across these 4 dimension. it looks like this:

=aggr( Concat( DISTINCT

if([ALL.alg-code]='ORG',if([ALL.alg-stat]='N','N',if([ALL.alg-stat]='Y','Y',)),null()),'-')

,[p-code],[PRODUCTS.p-desc],[PRODUCTS.p-size],PRODUCTS.origin)

combining aggr()  and if() like this could start to slow down your data model and if you have a lot of data i would look at seeing if you can pre-calculate the dimensions into the data model in the load script.

Let me know if it helps.

[JonnyPoole]

forgot to attach the qvw (now attached above)

[dmxmikey]

That’s absolutely perfect

Many Thanks for your help

Mike

Delivering the best in Foodservice...