Help to find the SOB

[RishabhTFC]

Hi, guys, I have a problem on finding the SOB of each Vendor according to thier  recent date.

I attached the Data below.

Basically, I want to find the SOB.(SOB= qty*sum( price of each product of latest date)  )

I want to draw a KPI that shows the total SOB

All I want is:

1) find the price pf latest date of each product.

2) Multiply the prices of products respective to their quantity.

3) add the all multiplied value of all products (only latest date price*qty) and show it in a KPI or Table

I attached the Excel sheet.

Thanks in Advance.

[jyothish8807]

Hi Shubham,

May be something like this:

Br,

KC

[RishabhTFC]

??

[jyothish8807]

refer the attachment.

[RishabhTFC]

Sorry, Jyotish I am unable to get your point.

[RishabhTFC]

No its not working

[RishabhTFC]

It doesn't show anything here. No sheet

[jyothish8807]

Try this:

Price:

Load *,

[Vendor ID_P]&Part_code_P&[Start Date_P]& Part_Vendor_Key as %Key;

LOAD [Vendor ID_P],

     Part_code_P,

     [Start Date_P],

     [End date_P],

     [Part Price_P],

     Part_Vendor_Key

FROM

(ooxml, embedded labels, table is Price_sample);

Join

BOM:

LOAD

Qty_B,

[Vendor ID_B]&Part_code_B&[Start Date_B]&Part_Vendor_Key as %Key;

LOAD [Vendor ID_B],

     Part_code_B,

     [Start Date_B],

     [End date_B],

     Qty_B,

     Part_Vendor_Key

FROM

(ooxml, embedded labels, table is BOM_sample);

At UI;

Crete a straight chart:

Dim: Part_code_P

Exp1: FirstSortedValue([Part Price_P],-[Start Date_P])

Exp2: FirstSortedValue([Part Price_P],-[Start Date_P])*Qty_B

Br,

KC

[jyothish8807]

[RishabhTFC]

Hello Jyotish, thank you very much yeah it's working.

I am new in qlik. So I just want to know can I use this number 168.1 in my KPI. I mean is there is any way a formula "measure" that I can Add it in my KPI . So that if there is multiple vendor like 3001, 3002,3002. and clicked on 3001 then it shows 168.1(total sob of all products of that vendor).

Thanks.

SG