To calculate the formulas

[Anonymous]

Hi Everyone,

I have a requirement where I need to calculate the GP% and $GP for my quotes, but somehow I'm getting the weird values. Can you help me with the stuff.

Formula:

GP% = (Resale- Cost)/Resale

$GP = Ext Resale - (Cost* Quantity).

Attached is the xls and qvf file.

Thanks and Regards,

Drishti Goel

[sunny_talwar]

What are the expected values?

[Anonymous]

Hey I attached the excel file where I calculated using the formula. You can see the expected values.

For me GP% is coming close to the value but $GP is always in negative. I have attached my excel files for the desired output and qvf file.

[sunny_talwar]

I didn't see any specific number you are looking for in the attached Excel file... but are you looking for this

GP% - 42% -> ((Sum(Resale - Cost))/Sum(Resale))

$GP - 99.43M? -> (Sum([Extended Resale USD])- (Sum(Cost*Quantity)))

[Anonymous]

Hi Sunny,

Thanks for your prompt reply, $GP is working fine as per the expected value.

GP% is still acting weird. To check for the if you filter on Bill to Customer Name in excel sheet and select Vais Technology and KS Technologies LLC, you can check on an average the GP % for both the customers is 23.20% but qlik shows 8%.

[sunny_talwar · Accepted solution]

May be this

Sum((Resale - Cost)/Resale)/Count([Line Number])

[Anonymous]

Yep it worked!

Thank you once again

[Anonymous]

Hi Sunny,

I had one more doubt. I'm trying to load the data below like this. I have to remove the duplicates in the column Bill to customer and Customer Part number. How can I achieve this?

[Sheet1]:

LOAD Distinct

[Quote Number],

[Branch],

[Bill To Customer EB Name],

[Bill To No],

[End Customer Number],

[End Customer Name],

[ISR Name],

    [Bill To Customer Name],

[FSR Name],

[Quote Header Creation Date],

[Quote Expiration Date],

[Type],

[PM],

[Assigned PM],

[Line Number],

[Cost],

[Resale],

    [Customer Part Number],

[PrAdj],

[Extended Resale USD],

[GM%],

[BPB],

[Quantity],

[Customer P/N Desc],

[Marketing Status],

[Sales Status],

[Internal Comment],

[QLR Comment],

[DW Status],

[DW Reg #],

[DW Type],

[DW Project],

[Part Number],

[Manufacturer],

[Segment 2],

[Item Status],

[Item Class],

[ICC Level 1],

[ICC Level 2],

[ICC Level 3],

[DW],

[NCNR],

[BSV],

[CR],

[SWB Link],

[FOH Qty],

[Product Mgr],

[QLR Date],

[Target Ext Cost USD],

[Target Ext Resale USD],

[Owned By],

APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([PrAdj]), '-') AS [Sheet1.PrAdj_GeoInfo]

FROM [lib://Documents/Open Quote Details .xlsx]

(ooxml, embedded labels, table is Sheet1);

[sunny_talwar]

Please open a new thread for a new requirement such as this. Also, it would be helpful if you can share few rows of data with what you expect to see from it in your new post.

Best,

Sunny

[Anonymous]

Thanks, I opened up a new thread - To remove the duplicates from two column and attach the data