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: 
Anonymous
Not applicable

Subtotals within Calculated Dimensions

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)

8 Replies
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
Not applicable
Author

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.

Excel.png

qlikviewwizard
Master II
Master II

Hi,

Can you attach some sample data to demonstrate your requirement?

Anonymous
Not applicable
Author

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
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
Not applicable
Author

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.Category as Expression.png

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