Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

dmxmikey
Contributor

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

Tags (1)
1 Solution

Accepted Solutions
Employee
Employee

Re: calculated dimension to show on one row

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.

10 Replies
Employee
Employee

Re: calculated dimension to show on one row

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
Valued Contributor III

Re: calculated dimension to show on one row

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
Contributor

Re: calculated dimension to show on one row

attached is my qvw

dmxmikey
Contributor

Re: calculated dimension to show on one row

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

Employee
Employee

Re: calculated dimension to show on one row

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

dmxmikey
Contributor

Re: calculated dimension to show on one row

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

Employee
Employee

Re: calculated dimension to show on one row

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.

Employee
Employee

Re: calculated dimension to show on one row

forgot to attach the qvw (now attached above)

dmxmikey
Contributor

Re: calculated dimension to show on one row

That’s absolutely perfect

Many Thanks for your help

Mike

Delivering the best in Foodservice...

Community Browser