Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
dmxmikey
Creator
Creator

calculated dimension to show on one row

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

1 Solution

Accepted Solutions
JonnyPoole
Employee
Employee

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.

View solution in original post

10 Replies
JonnyPoole
Employee
Employee

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
Partner - Specialist III
Partner - Specialist III

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
Creator
Creator
Author

attached is my qvw

dmxmikey
Creator
Creator
Author

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
Employee
Employee

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

dmxmikey
Creator
Creator
Author

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
Employee
Employee

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
Employee
Employee

forgot to attach the qvw (now attached above)

dmxmikey
Creator
Creator
Author

That’s absolutely perfect

Many Thanks for your help

Mike

Delivering the best in Foodservice...