Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dgoel1991
New Contributor II

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

Re: To calculate the formulas

May be this

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

10 Replies

Re: To calculate the formulas

What are the expected values?

dgoel1991
New Contributor II

Re: To calculate the formulas

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.

Re: To calculate the formulas

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)))

dgoel1991
New Contributor II

Re: To calculate the formulas

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%.

Re: To calculate the formulas

May be this

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

dgoel1991
New Contributor II

Re: To calculate the formulas

Yep it worked!

Thank you once again

dgoel1991
New Contributor II

Re: To calculate the formulas

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);

Re: To calculate the formulas

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

dgoel1991
New Contributor II

Re: To calculate the formulas

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

Community Browser