topic Re: How to group the measures? in QlikView

How to group the measures?

suvechha_b — Thu, 14 Nov 2013 11:25:54 GMT

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.

Re: How to group the measures?

israrkhan — Thu, 14 Nov 2013 11:37:55 GMT

have you heard about cyclic group?

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

Re: How to group the measures?

kaushiknsolanki — Thu, 14 Nov 2013 12:39:24 GMT

Hi,

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

Are they Expressions?

Regards,

Kaushik Solanki

Re: How to group the measures?

rustyfishbones — Thu, 14 Nov 2013 12:42:44 GMT

Re: How to group the measures?

suvechha_b — Thu, 14 Nov 2013 12:43:43 GMT

they are expression.

Re: How to group the measures?

kaushiknsolanki — Thu, 14 Nov 2013 12:45:46 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: How to group the measures?

sudeepkm — Thu, 14 Nov 2013 13:04:07 GMT

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.

Re: How to group the measures?

suvechha_b — Thu, 14 Nov 2013 13:11:09 GMT

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

Re: Re: How to group the measures?

suvechha_b — Thu, 14 Nov 2013 13:21:06 GMT

See the snapshot now.

Re: How to group the measures?

sebastiandperei — Thu, 14 Nov 2013 13:45:19 GMT

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

Re: Re: How to group the measures?

suvechha_b — Fri, 15 Nov 2013 09:31:17 GMT

Hi ,

I have tried this in inline :

LOAD * INLINE [

Group,name

SupplyPlan,Opening Stock On Hand

SupplyPlan,Budget Recommended Order

SupplyPlan,Recommended Order

SupplyPlan,Inbound Orders

SupplyPlan,Actual Receipts

SupplyPlan,Adherence to plan(Factory)

SalesPlan,Sales Budget Units

SalesPlan,Sales Plan(Planner Forecast)

SalesPlan,Actual Sales

SalesPlan,Customer Orders

SalesPlan,Adherence to Plan(MAPE3)

SalesPlan,Adherence to Plan(ARITH1)

SalesPlan,Model Stock Units

Inventory,Days On Hand

Inventory,Projected Adjustment

Inventory,Closing Stock On Hand

Inventory,Safety Stock Units

Inventory,Opening Stock on Hand Horizon Inbound Orders

Inventory,Full Horizon Inbound Orders

Inventory,Closing Stock on Hand Horizon Inbound Orders

Inventory,RISK

Inventory,Blocked Stock

];

I am near to my report . but I got stuck in calculating a field :

Please see my expression :

if(name= 'Opening Stock On Hand', sum(opening_stock_unit),

if(name= 'Budget Recommended Order',sum(budget_recommended_order_quantity),

if(name= 'Recommended Order',sum(recommended_order),

if(name= 'Inbound Orders',sum(inbound_orders),

if(name= 'Actual Receipts', sum(purchase_receipts),

if(name= 'Adherence to plan(Factory)',(sum(purchase_receipts)/sum(inbound_orders)*100),

if(name= 'Sales Budget Units',sum(sales_budget_units),

if(name= 'Sales Plan(Planner Forecast)',sum(sales_plan),

if(name= 'Actual Sales', sum(sale_units),

if(name= 'Customer Orders',sum(customer_order),

if(name= 'Adherence to Plan(MAPE3)',(100-fabs(sum(error))/Sum([actual])*100),

if(name= 'Adherence to Plan(ARITH1)',(Sum([Actual Units])/Sum([Forecast Units])*100),

if(name= 'Model Stock Units', sum(model_stock_units),

if(name= 'Days On Hand',sum(days_on_hand),

if(name= 'Projected Adjustment',Sum(projected_adjustment_unit),

if(name= 'Closing Stock On Hand',sum([closing_stock_unit]),

if(name= 'Opening Stock on Hand Horizon Inbound Orders',Sum(OpeningStockOnHandHorizon),

if(name= 'Full Horizon Inbound Orders',Sum(FullHorizonInbounds),

if(name= 'Closing Stock on Hand Horizon Inbound Orders', Sum(ClosingStockOnHandHorizon),

if(name= 'RISK',RangeMin(0, $(vRisks)-$(vUnit)),Sum(qa_blocked_stock)))))))))))))))))))))

everything is working fine ecxcept ( the bolded expression)

if(name= 'Adherence to Plan(MAPE3)',(100-fabs(sum(error))/Sum([actual])*100),

and

if(name= 'RISK',RangeMin(0, $(vRisks)-$(vUnit))

as this two is expression contain variable that why I am unable to get the result.

can someone help how to calculate MAPE3 : max(0,100% - fabs(sum(error))/Sum([actual])*100) ??

and how to put RISK calculation ??

Re: Re: How to group the measures?

suvechha_b — Fri, 15 Nov 2013 09:32:31 GMT

Hi ,

I have tried this in inline :

LOAD * INLINE [

Group,name

SupplyPlan,Opening Stock On Hand

SupplyPlan,Budget Recommended Order

SupplyPlan,Recommended Order

SupplyPlan,Inbound Orders

SupplyPlan,Actual Receipts

SupplyPlan,Adherence to plan(Factory)

SalesPlan,Sales Budget Units

SalesPlan,Sales Plan(Planner Forecast)

SalesPlan,Actual Sales

SalesPlan,Customer Orders

SalesPlan,Adherence to Plan(MAPE3)

SalesPlan,Adherence to Plan(ARITH1)

SalesPlan,Model Stock Units

Inventory,Days On Hand

Inventory,Projected Adjustment

Inventory,Closing Stock On Hand

Inventory,Safety Stock Units

Inventory,Opening Stock on Hand Horizon Inbound Orders

Inventory,Full Horizon Inbound Orders

Inventory,Closing Stock on Hand Horizon Inbound Orders

Inventory,RISK

Inventory,Blocked Stock

];

I am near to my report . but I got stuck in calculating a field :

Please see my expression :

if(name= 'Opening Stock On Hand', sum(opening_stock_unit),

if(name= 'Budget Recommended Order',sum(budget_recommended_order_quantity),

if(name= 'Recommended Order',sum(recommended_order),

if(name= 'Inbound Orders',sum(inbound_orders),

if(name= 'Actual Receipts', sum(purchase_receipts),

if(name= 'Adherence to plan(Factory)',(sum(purchase_receipts)/sum(inbound_orders)*100),

if(name= 'Sales Budget Units',sum(sales_budget_units),

if(name= 'Sales Plan(Planner Forecast)',sum(sales_plan),

if(name= 'Actual Sales', sum(sale_units),

if(name= 'Customer Orders',sum(customer_order),

if(name= 'Adherence to Plan(MAPE3)',(100-fabs(sum(error))/Sum([actual])*100),

if(name= 'Adherence to Plan(ARITH1)',(Sum([Actual Units])/Sum([Forecast Units])*100),

if(name= 'Model Stock Units', sum(model_stock_units),

if(name= 'Days On Hand',sum(days_on_hand),

if(name= 'Projected Adjustment',Sum(projected_adjustment_unit),

if(name= 'Closing Stock On Hand',sum([closing_stock_unit]),

if(name= 'Opening Stock on Hand Horizon Inbound Orders',Sum(OpeningStockOnHandHorizon),

if(name= 'Full Horizon Inbound Orders',Sum(FullHorizonInbounds),

if(name= 'Closing Stock on Hand Horizon Inbound Orders', Sum(ClosingStockOnHandHorizon),

if(name= 'RISK',RangeMin(0, $(vRisks)-$(vUnit)),Sum(qa_blocked_stock)))))))))))))))))))))

everything is working fine ecxcept ( the bolded expression)

if(name= 'Adherence to Plan(MAPE3)',(100-fabs(sum(error))/Sum([actual])*100),

and

if(name= 'RISK',RangeMin(0, $(vRisks)-$(vUnit))

as this two is expression contain variable that why I am unable to get the result.

can someone help how to calculate MAPE3 : max(0,100% -fabs(sum(error))/Sum([actual])*100) ??

and how to put RISK calculation ??

Re: Re: Re: How to group the measures?

Fri, 15 Nov 2013 09:59:48 GMT

this is the solution if you want to group your expression.

Check your closing ) if they are right.