QlikView - how to apply different gross margin % to different plants

[#1]

Hi

I am new to QlikView and would like to ask how to perform calculation as example below :

Plant	Month	Gross Margin %
A	Sep-13	10%
A	Oct-13	17%
A	Nov-13	14%
A	Dec-13	18%
B	Aug-13	19%
B	Sep-13	19%
B	Oct-13	15%
B	Nov-13	19%

Plant	Date	Sales
A	19/9/2013	264.72
A	20/9/2013	590.48
A	21/9/2013	751.19
A	13/8/2013	607.21
B	14/8/2013	944.56
B	15/8/2013	335.48
B	6/7/2013	162.15
B	1/6/2013	892.66

So that gross margin can be computed :

Plant	Month	Gross Margin
A	Sep-13	283.71
B	Aug-13	238.58

Currently these data is residing in 2 different tables; and I tried to use set analysis & variable to do the operations similar to Excel array formula but was not successful. Please help.

[spividori]

Hi.

Could you explain how to calculate gross margin, eg A Sep-2013?, As do you know that the result is 283.71?

Regards.

[#3]

Hi Sandro,

Thanks for the reply!

Actually there is some error when I copy & paste the values from Excel as I was using random function.

So e.g. for plant A in Sep 2013 the gross margin should be (264.72 + 590.48 + 751.19) x 10% = 160.64

I used below formula to extract the gross margin % from database :

sum({<Year={$(=max(Year))},Month={$(=num(Month( now())))},Account={GPRatio} >} Figure)

whereas aggr(vSales, plant) as sales. vSales is a variable = sum({<Year={$(=max(Year))},Month={$(=num(Month( now())))} >} Sales)

so on plant level (when selection is done) is correct but group level is wrong as it 'sum' up all gross margin %.

Regards,

Eric

[jagan]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[#5]

Hi Jagan,

Thanks for the reply!

Actually I am looking for value to be shown in a Textbox. I have modified your file. Pls. refer to attached.

I have a plant selector. If I don't select it (that means I want to see total) then the value inside blue Textbox is blank. If instead I choose either plant A or B then I can see each value.

Pls. help.

Regards,

Eric

[Anonymous]

Try this:

T1:
LOAD * INLINE [
Plant, Month, GM,
A, Sep-13, 10%,
A, Aug-13, 10%,
A, Oct-13, 17%,
A, Nov-13, 17%,
A, Dec-13, 17%,
A, Jul-13, 17%,
A, Jun-13, 17%,
B, Aug-13, 17%,
B, Sep-13, 17%,
B, Oct-13, 17%,
B, Nov-13, 17%,
B, Jul-13, 17%,
B, Jun-13, 17%,
];

Map_T2:
MAPPING LOAD
Plant&'|'&Month as [Plant Month],
GM
Resident T1;

T2:

LOAD * INLINE [
Plant, Date, Sales,
A, 19/9/2013,264,
A, 20/9/2013,590 ,
A, 21/9/2013,751, 
A, 13/8/2013,607,
B, 14/8/2013,944,
B, 15/8/2013,335,
B, 6/7/2013,162,
B, 1/6/2013,892,
];

T3:
LOAD

     Plant,
     Date,
     Sales,
     Month(Date)&'-'&Date(Date,'YY') as MonthDate,
     ApplyMap('Map_T2', Plant&'|'&Month(Date)&'-'&Date(Date,'YY'), 'Not yet defined') as GM

Resident T2;

T4:
LOAD
Plant,
    MonthDate,
    GM,
    Sales,
    GM*Sales as [GM $]

Resident T3;

T5:
Load
Plant,
MonthDate,
Sum([GM $]) as [Total Margin]
Resident T4
Group By Plant, MonthDate;

DROP Table  T1, T2, T3, T4;

[jagan]

Hi,

Use this expression in Text object

=Sum(Aggr(Sum(Sales) * GrossMarginPer, Plant))

Regards,

Jagan.

[spividori]

HI.

In Text object try:

=Sum(Sales * GrossMarginPer)

Regards.

[#9]

Thanks eruditio, Jagan & Sandro! I will study your advices and revert later.