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.
GP% = (Resale- Cost)/Resale
$GP = Ext Resale - (Cost* Quantity).
Attached is the xls and qvf file.
Thanks and Regards,
Solved! Go to Solution.
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.
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)))
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%.
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?
[Bill To Customer EB Name],
[Bill To No],
[End Customer Number],
[End Customer Name],
[Bill To Customer Name],
[Quote Header Creation Date],
[Quote Expiration Date],
[Customer Part Number],
[Extended Resale USD],
[Customer P/N Desc],
[DW Reg #],
[ICC Level 1],
[ICC Level 2],
[ICC Level 3],
[Target Ext Cost USD],
[Target Ext Resale USD],
APPLYMAP( '__countryCodeIsoTwo2Polygon', UPPER([PrAdj]), '-') AS [Sheet1.PrAdj_GeoInfo]
FROM [lib://Documents/Open Quote Details .xlsx]
(ooxml, embedded labels, table is Sheet1);
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.