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: 
suvechha_b
Creator III
Creator III

How to group the measures?

How to group my measures like :

1.Openning Stock On hand

2.Budget Recommended Order

3.Recommended Order

4.Inbound Orders

in one group like Supply Plan

and

5.Actual Receipt

6.Adherence to Plan (Factory)%

7.Sales Budget Unit

in other group name Sales Plan.

Note :

All my measures are calculative field got from different view . They all are expressions.

And Product Code,Product, Commercial Manager are Dimensions

I want to see my file like :

Product Code,Product, Commercial Manager , Group, Bucket Description sequence.

I am giving a snapshot of my report.

Need Help.

Thanks In Advance.

12 Replies
israrkhan
Specialist II
Specialist II

have you heard about cyclic group?

i think cyclic group can help you to group your Measures..

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     As per the image it seems that they are Values of a field?

     Are they Expressions?

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
rustyfishbones
Master II
Master II

2013-11-14_1229.png

suvechha_b
Creator III
Creator III
Author

they are expression.

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

     I dont think so they can be group the way you want.

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sudeepkm
Specialist III
Specialist III

I assume that "Bucket Description" is a field in the main data model table. whose field values are

Openning Stock On hand ,Budget Recommended Order,Recommended Order,Inbound Orders, Actual Receipt,Adherence to Plan (Factory)% and Sales Budget Unit,

Please try like below:

Create an Inline Load:

BuckDesc:

load * inline [

Bucket DescriptionFinal, Bucket Description

Supply Plan,Openning Stock On hand

Supply Plan,Budget Recommended Order

Supply Plan,Recommended Order

Supply Plan,Inbound Orders

Openning Stock On hand,Openning Stock On hand

Budget Recommended Order,Budget Recommended Order

Recommended Order,Recommended Order

Inbound Orders,Inbound Orders

Sales Plan, Actual Receipt

Sales Plan, Adherence to Plan (Factory)%

Sales Plan, Sales Budget Unit

Actual Receipt, Actual Receipt

Adherence to Plan (Factory)%, Adherence to Plan (Factory)%

Sales Budget Unit, Sales Budget Unit

];

now you can use the "Bucket DescriptionFinal" as your dimension.

suvechha_b
Creator III
Creator III
Author

Bucket Description is the time period i.e Jul 2013 , Aug 2013  and so on..

Opening Stock On Hand is an expression which is calculated like sum(opening_stock_hand),

Budget Recommended Order : sum (budget_recommended_order) and so on...

suvechha_b
Creator III
Creator III
Author

See the snapshot now.

sebastiandperei
Specialist
Specialist

Hi. The only way i found to group expressions (i think it's that you need, groups like Products, or Comercial manager) isn't very easy.

1- You need to create a table (i recommend to do in excel, for simply future upgrades)  like that:

exp_ord     exp_group        exp_name                                   exp_expression

1               Supply Plan     Openning Stock On hand             sum(opening_stock_hand)

2               Supply Plan     Budget Recommended Order       sum (budget_recommended_order)

3               Supply Plan     Recommended Order                    ...

4              Supply Plan     Inbound Orders                              ...

With all expression you need, with their groups. You can also put a field with color, format, etc. for each expression, if you want.

2- Read it in a table. For example, Exp.

3- Now, you must create a variable with a "Pick" functions, that points to each expression. To do that, use concat in an auxiliar table, and then read it with "peek", like follows:

Aux:

Load Distinct

  'Pick(exp_ord,'&Concat(exp_expression,',',exp_ord)&')' as vExp

Resident Exp;

Let vExp = Peek('vExp');

Drop table Aux;

vExp should contain:

Pick (exp_ord, sum(opening_stock_hand), sum (budget_recommended_order), ....)

4- For ending, your table will contain:

Dimmentions:

Product_Code

Product

Commercial Manager

exp_group

exp_name

Bucket

Expressions:

$(vExp)

Order:

First 6 dimmentions by exp_order, then last like you want.

You could use format and color fields in the expression to make differences between them.

It isn't very simple, but is the only way i found! Please, let me know if it was useful for you!!