topic Help to find the SOB in QlikView

Help to find the SOB

RishabhTFC — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Help to find the SOB

jyothish8807 — Thu, 21 Jun 2018 12:18:44 GMT

Hi Shubham,

May be something like this:

Br,

Re: Help to find the SOB

RishabhTFC — Thu, 21 Jun 2018 12:21:52 GMT

Re: Help to find the SOB

jyothish8807 — Thu, 21 Jun 2018 12:23:05 GMT

refer the attachment.

Re: Help to find the SOB

RishabhTFC — Thu, 21 Jun 2018 12:48:44 GMT

Sorry, Jyotish I am unable to get your point.

Re: Help to find the SOB

RishabhTFC — Thu, 21 Jun 2018 12:50:01 GMT

No its not working

Re: Help to find the SOB

RishabhTFC — Thu, 21 Jun 2018 12:53:53 GMT

It doesn't show anything here. No sheet

Re: Help to find the SOB

jyothish8807 — Thu, 21 Jun 2018 12:57:09 GMT

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,

Re: Help to find the SOB

jyothish8807 — Thu, 21 Jun 2018 12:58:08 GMT

Re: Help to find the SOB

RishabhTFC — Fri, 22 Jun 2018 09:17:36 GMT

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.