I am new to QLIK view, trying to develop my own dashboard. I am struck with Sum product formula, let me explain my case.
I have each market wise cost and volume, i want to know the overall Region cost. I have given below the sample data. In excel i will write formula as =sum product(Cost,Volume)/Total Volumes. please try to translate it to expression in QLIkview
|Middle East & Africa Total||3,053.33||150000|
Solved! Go to Solution.
Thanks stalwar1 However i just noticed that i have more items like this. For e.g assume the above given data is for Material cost, likewise i have 20 different cost like Lab our cost, selling cost, admin cost, marketing cost etc.
In the same format cost were appended one after another. In this case, if i need to get the same formula for one of the like items Selling cost, what i am i suppose to do.
I am trying to create a income statement for my product, how it will perform based on the product life cycle.
I want to report Per product cost, $ mils and in %, so created 3 different columns to report it, hence i have chosen cyclic view.
The problem with cyclic view is. It is summing up the total which will work out for $ mils, just sum of all region. However for per product cost, we need to use the sum product of respective line items (for eg. Admin exp) totals of all market and the volumes of respective market divided by overall volume. This is where i am struck.
I am happy if i can get this in KPI itself, so that i will remove the partial sum in my pivot. I have attached my sample data and QLIK view dashboard and dummy Inc statement.
Let me know if anybody can help me.
For example, at the top MEA total (Cycle average) i expected to see is 1530 which is
cyc avg - sum product (Dubai + North Africa + South Africa + Other Africa : Volume(Dubai+N.Africa+ S.Africa+ O.Africa) / Total Cyc avg volume
In numbers = sumproduct(2914+192+2423+129 : 2927+4098+15220+6439)/28683 = 1530
Similarly, each year should work in the same logic, when each item in the left is selected. When nothing selected, it should consider as Automotive PBT as selected and fetch the results of "Automotive PBT" as final number.
You are correct, i recognize the 1639. My excel calculation was wrong. Can you share the qvw file, i will to understand how the expressions are written and learn from it.
I wanted to create KPI for this data, which should give me $ Mils, Per Product cost & % for Automotive PBT. Can you help on that as well?
I need your guidance on the following points as well
1. I have more products, but same logic. Can i append the new product data? or any better suggestions
2. Currently i have listed this month status alone, when i need to see other months data of same product. Is it OK to append or any recommendation from your side?