Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Need help in calculating Sales Profit using Ratio Analysis

Hello All,

I am new user of Qlikview and I had start working Qlikview Personal Edition (free) with test data, while generating stock transaction report I had found query:

Tesing Value

Sales Master Table:

Sales Master
UniqueCodeDocNoDocDateShopCodeSalesManCodeTotalNoOfPcsTotalSalesAmt DocDiscount ReceivedAmt
35526629/09/2012JBLAB3            30,000.00           1,500.00             28,500.00

Sales Detail Table:

 

Sales Details
UniqueCodeDocNoStock CodeShopCodeSalesManCodeNoifItemsStockTypeCostPriceSalesValue DocDiscount Profit %
355266D00001JBLAB1D   3,000.00          9,000.00             1,500.00 ?
355266D00002JBLAB1D   5,000.00       17,000.00             1,500.00 ?
355266G00001JBLAB1G   1,000.00          4,000.00             1,500.00   N/A

To fetch data I had followed

LOAD

SQL SELECT

Method for both table

Now to finish my report I would like to learn from you guys that how can:

How I can divide document discount between two items (where stocktype is 'D')

My calcuation in excel will be like :

SalesValueDocDiscountSalesValue in %ItemWiseDiscountSalesValueAfDiscountProfit %
     9,000.00 34.62519.23                                  8,480.77
  17,000.00         1,500.00 65.38980.77                                16,019.23
  26,000.00                        1,500.00                                 24,500.00

During my testing I am finding very hard to perform calcuation of SalesValue in %, ItemWiseDiscount,

I HAD GONE THROUGH SOME POST AND FOUND THAT AGGR FUNCTION CAN HELP ME TO PRODUCE RESULT IF YES WILL ANYONE CAN GUIDE ME  HOW TO DO THAT ?

AFTER POSING MESSAGE FIRST TIME I HAD FOUND THAT ABOVE MENTION TABLE IS NOT CLEAR SO I ADD EXAMPLE IN EXCEL SHEET.

THANKS IN ADVANCE.

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Need help in calculating Sales Profit using Ratio Analysis

HI,

     Have a look at the attched Application.

     You can use below expression to calulate the discount.

     sum(aggr(sum({<[Item Type] = {"D"}>}DocDiscount) * num(Sum({<[Item Type] = {"D"}>}[Sales Price]) / Sum({<[Item Type] = {"D"}>} total<LocationCode> [Sales Price]),'#.##0.00%'),Tran_Year,Tran_Month,VoucherNo, LocationCode,Stock_Code))

     This will help you to get the discount as you want.

     Then once you find discount, I am sure you can calculate the other KPI's too.

     Cheers

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

5 Replies
Highlighted

Re: Need help in calculating Sales Profit using Ratio Analysis

First, you should load both tables as separate tables. Probably you should keep "UniqueCode" in both tables (as key), but remove "DocNo", "ShopCode", "SalesManCode" and "DocDiscount" from the details table. Just don't include them in the SELECT.

Then you can use a chart to do the calculation. You probably want "UniqueCode" and "StockCode" as dimensions. The expression "Sum(SalesValue)/Sum(total <UniqueCode> SalesValue)" will calculate the relative part of the salesvalue within the UniqueCode. So the expression

"Sum(DocDiscount)*Sum(SalesValue)/Sum(total <UniqueCode> SalesValue)" will calculate the relative part of the DocDiscount.

I am not sure how you want to calculate the profit (your Excel sheet wasn't clear either...) but my guess is

Sum(SalesValue)-Sum(CostPrice)-Sum(DocDiscount)*Sum(SalesValue)/Sum(total <UniqueCode> SalesValue)

HIC

Highlighted
Not applicable

Re: Need help in calculating Sales Profit using Ratio Analysis

Thanks for the reply,

First of all sorry if there is massup in excel sheet and it is creating miss understanding.

To get to same page I think I should go into more details.

I am preparing pivot table as chart type and trying to get my result.

On pivot I have below mention groups:

by year,          (First Dimension )

by branch,       (When I expend it will show details by branch wise result)

by divisions,     (like branch wise other below mention group shows result)

by document no, (like branch wise other below mention group shows result)

by item code,  

When I expend last last Dimension,  it should show require calculation by each individual item code (stock code).

In my result I have plan to have calculations (Cost Value, Sales Value Before discont, Sales Value after discont, Profit value in amt, Profit value in % etc.

I belive what I am doing is most common requirements in bi and my problem is that I am new and because of that  I don't know how to stracture the result in qlickview.

Even if anyone know similar stracture in any example then let me know url so i can dowload and explore.

Once again thanks for reply.

Highlighted
Not applicable

Re: Need help in calculating Sales Profit using Ratio Analysis

I am attaching testing example in which I have two excel file and one qlickview file.

My requirements also mention on pivot table blak fields

TransactionMaster.xlsx   - Example of Master Table

TransactionDetails.xlsx - Example of Transaction Details Table

QlikViewPETesting.qvw  - QlickView testing File

As mention on QVW file :

I would like to see below mention calculation on Sales Price AFD (After Discount) expression:

Please guys can you look into this and help to get out from my issue.

vocher no 1 is having total 3 items but I want devide docment discount only for two items (itemtype = 'D') 
So to do that I am calculating

ratio of sales % for two items 

First stock

STKD0001        (6000/13000) * 100 where my sales ratio will be 46.15%

STKD0002        (7000/13000) * 100 where my sales ratio will be 53.85%

Now I have Sales ratio % so I can divide document discount:

STKD0001     1500 * 46.15 / 100 where I will get discount value   692.25

STKD0002     1500 * 53.85 / 100 where I will get discount value   807.75

now in last Sales Vaule - Discount value will be my Sales Price after discont.

I have tried to make make easy as possible for the person who likes to help me.

Because of beginer I am not able to do above mention calculation. 

Highlighted
MVP & Luminary
MVP & Luminary

Re: Need help in calculating Sales Profit using Ratio Analysis

HI,

     Have a look at the attched Application.

     You can use below expression to calulate the discount.

     sum(aggr(sum({<[Item Type] = {"D"}>}DocDiscount) * num(Sum({<[Item Type] = {"D"}>}[Sales Price]) / Sum({<[Item Type] = {"D"}>} total<LocationCode> [Sales Price]),'#.##0.00%'),Tran_Year,Tran_Month,VoucherNo, LocationCode,Stock_Code))

     This will help you to get the discount as you want.

     Then once you find discount, I am sure you can calculate the other KPI's too.

     Cheers

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Highlighted
Not applicable

Re: Need help in calculating Sales Profit using Ratio Analysis

Thanks it is working as expacted.