Need to Create KPI's

[shannoypaul]

Hi all,

KPI's on the basis of availability of Tender Prices.

Relationship

Bidder - Tender Name

One-to-Many i.e. One Bidder can have 1 or more than 1 Tenders (Tender Name)

Tender Name - Tender Details

One-to-Many i.e. One Tender can have 1 or more than 1 Tender Lines (Tender Details)

Tender Details - Tender Price

One-to-One i.e. One Tender Line (Tender Details) can have only one Tender Price

KPI's to be built on the basis of Tender Price Available v/s Total Tender Prices (Available + Blank).

Attached an excel for better understanding.

TIA,

Shannoy

[neelamsaroha157]

Can you elaborate a bit more what you want as output

[sasikanth · Accepted solution]

HI,

Try this

Script:
LOAD //[Sr No.],
    //[Tender Name],
    IF(IsNull([Tender Name]),Peek('Tendername'),[Tender Name] ) as Tendername,
    [Tender Details],
    [Tender Bidder],
    [Tender Price (in INR)],
    F as TPRICE
FROM
[..\Downloads_Web\Tender KPI.xlsx]
(ooxml, embedded labels, table is [Tender Details]);

Exp: count({<TPRICE-={0}>} TPRICE)/count( {1}TPRICE)

Attached Application for reference