Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

To calculate the formulas

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

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

10 Replies
sunny_talwar

What are the expected values?

Anonymous
Not applicable
Author

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
Not applicable
Author

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

May be this

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

Anonymous
Not applicable
Author

Yep it worked!

Thank you once again

Anonymous
Not applicable
Author

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
Not applicable
Author

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