Subtotals within Calculated Dimensions

[Anonymous]

I'm new to qlik and was working with a calculated dimension.  I've got a portion of it pasted below.  I'm basically using the dimension to create subcategories to group certain account numbers into certain categories.  Is there a way to add subtotals of the categories?  For example I'm trying to add a line showing Category1 and Category2 added together before displaying category3.  I'm not sure that the calculated dimension is the best approach to what I'm trying to do so any help or suggestions would be appreciated.  Thanks.

=if(
Left([OA NL Transaction Expense Code],1) = 4,
dual('Category1' ,1),

if(match([OA NL Transaction Expense Code],'50000','50005','50050','50055','50060','50065','50070','50075','50080','50085','50090','50300','58100'),
dual('Category2',2),

if(match([OA NL Transaction Expense Code],'50010','50015','50020','50025','51925'),
dual('Category3',3)

[sunny_talwar]

There might be no direct way, but you can add an island table and play around with that to get this to work....Would you be able to share some data to help you better here?

[Anonymous]

I've attached a screenshot of what I've got working so far.  All of my data comes from an ERP system.  I've got a calculated dimension that is looking at account codes and putting them into categories.  I've also got the account code and description as dimensions.  My expression just pulls the values.  I'm looking for a subtotal between categories.  My end goal is to build a P&L from qlik.

[qlikviewwizard]

Hi,

Can you attach some sample data to demonstrate your requirement?

[Anonymous]

What type of data would be helpful?  I apologize but I'm very new to Qlik and don't know what would be helpful.

[Anil_Babu_Samineni]

Better if you can share inline data or that image in excel to work on it with QVW (To work same application). As sunny said we can create island table

[marcus_sommer]

I think by your description that you need multiple expressions to show these not fully hierarchy totals maybe by using a island-table or a valuelist() for the dimension like:

dim:

load * inline [

dim

cat1

cat2

total cat 1 & 2

cat3

...

];

and then an expression like:

pick(match(dim, 'cat1', 'cat2', 'total cat 1 & 2', 'cat3', ...),

     sum(x),

     sum(y),

     sum(z),

     ...)

Further I could imagine that something like this will bring some good ideas: How to Create a Profit and Loss Statement in QlikView.

- Marcus

[Anonymous]

Marcus,

I also tried playing around and making my category an expression.  When I do that I get a different problem.  Each expression is duplicated.  Once with values and once without.  I also reviewed the link you found.  I'll have to look at it in more detail but up front it looks like that talks about creating a P&L by loading an excel.  Being that all my data is coming from an ERP system I'm not sure how to relate to it.

[marcus_sommer]

My suggestion from above was for one dimension. If this approach should be applied to multiple dimensions the logic needs to be extended too, for example:

dim:

load *, dim & dim2 as KEY inline [

dim, dim2

cat1, x

cat2, y

total cat 1 & 2, x

total cat 1 & 2, y

cat3, z

...

];

and then an expression like:

pick(match(KEY, 'cat1x', 'cat2y', 'total cat 1 & 2x', ...),

     sum(x),

     sum(y),

     sum(z),

     ...)

but before doing it I would re-think the whole datamodel because in my opinion this kind of categorizing / matching from various categories should be rather an integral part of the datamodel and not that such artificial structure is used to build objects and to fetch the right fields and conditions within the expressions. Therefore take a further look in the link above and there are surely other similar postings from you could get ideas how tohandle your task.

- Marcus