topic Re: calculated dimension to show on one row in QlikView

calculated dimension to show on one row

dmxmikey — Wed, 29 Oct 2014 16:44:44 GMT

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

Re: calculated dimension to show on one row

JonnyPoole — Wed, 29 Oct 2014 16:52:41 GMT

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.

Re: calculated dimension to show on one row

jpenuliar — Wed, 29 Oct 2014 16:55:40 GMT

Hi,

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

You could change that to a Straight Table Chart Obj

Re: calculated dimension to show on one row

dmxmikey — Wed, 29 Oct 2014 16:59:51 GMT

attached is my qvw

Re: calculated dimension to show on one row

dmxmikey — Wed, 29 Oct 2014 17:01:26 GMT

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

Re: calculated dimension to show on one row

JonnyPoole — Wed, 29 Oct 2014 17:04:25 GMT

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

Re: calculated dimension to show on one row

dmxmikey — Wed, 29 Oct 2014 17:11:40 GMT

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

Re: calculated dimension to show on one row

JonnyPoole — Wed, 29 Oct 2014 19:21:31 GMT

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.

Re: calculated dimension to show on one row

JonnyPoole — Wed, 29 Oct 2014 19:29:30 GMT

forgot to attach the qvw (now attached above)

Re: calculated dimension to show on one row

dmxmikey — Thu, 30 Oct 2014 09:19:31 GMT

That’s absolutely perfect

Many Thanks for your help

Mike

Delivering the best in Foodservice...

Re: calculated dimension to show on one row

JonnyPoole — Thu, 30 Oct 2014 12:30:03 GMT

Good to hear. Would you mind closing the post?