topic Re: Sum product in QlikView

Sum product

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

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

Region	Cost	Volumes
Dubai	1,000.00	2000
Israel	-	0
Northern Africa	2,000.00	8000
Southern Africa	3,000.00	120000
Other Africa	4,000.00	20000

Middle East & Africa Total	3,053.33	150000

Re: Sum product

sunny_talwar — Thu, 29 Mar 2018 14:32:03 GMT

Try this

Sum(Cost*Volume)/Sum(Volume)

Re: Sum product

Anonymous — Thu, 29 Mar 2018 15:46:53 GMT

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.

Re: Sum product

sunny_talwar — Thu, 29 Mar 2018 15:48:27 GMT

I am not sure I follow... do you mind sharing the new data that you have?

Re: Sum product

sunny_talwar — Thu, 29 Mar 2018 15:48:49 GMT

For attaching, check here

Uploading a Sample

Re: Sum product

Anonymous — Thu, 29 Mar 2018 16:37:44 GMT

Sunny,

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.

Regards

Ramesh

Re: Sum product

sunny_talwar — Thu, 29 Mar 2018 16:46:41 GMT

So, based on the sample data that you have attached, I guess you are having issue when Per Unit is selected, right? Is the problem here?

What is the number you expect to see here?

Re: Sum product

Anonymous — Thu, 29 Mar 2018 17:03:42 GMT

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.

Re: Sum product

sunny_talwar — Thu, 29 Mar 2018 17:14:25 GMT

Not sure how you are getting 1530... I got 1639 when I did the above calculation is Excel... Do you agree with 1639?

Re: Sum product

Anonymous — Fri, 30 Mar 2018 05:57:21 GMT

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?

Thanks

Ramesh

Re: Sum product

sunny_talwar — Fri, 30 Mar 2018 12:30:25 GMT

If you open this link Re: Sum product... you will see the qvw attached with my response.

When you say in KPI, those only show value when one Line Item Grouping is selected?

1) I think that's how it should work and if you want to look at product you can either add it as a dimension or make a selection to view a single product.

2) I am not sure I understand this

Re: Sum product

Anonymous — Sat, 31 Mar 2018 04:03:54 GMT

Thanks Sunny, you are awesome. I can take this for my internal review with my management.

My plan is to develop 3 KPI

1. Profit Margin (in%) which is nothing but Automotive PBT / (Net revenue + G.Revenue *10%) of my data set

2. In the flash wanted to give the PBT in all the means ($ Mils, Per unit & %)

3. Wanted to show the same KPI as in point 2 when the new product is launched (Launched year)

Re: Sum product

tripatirao — Sat, 31 Mar 2018 11:45:26 GMT

Hi Sunny,

why you used Dimensionality() here.

Please let me know

Re: Sum product

sunny_talwar — Sat, 31 Mar 2018 20:58:51 GMT

1) For which year would want to show this for?

2) When you say Flash, do you mean it toggles between the three based on some time passed like every 3-4 seconds the value changes?

3) Another set of text boxes for new product? but what is the definition of new product?

Re: Sum product

Anonymous — Mon, 02 Apr 2018 06:04:36 GMT

Sunny,

My company is manufacturing company, They launch multiple products over period of time which has different product cycle. For e.g take case of Samsung, each were different products and they are launching month over month or year over year according to the regional demand and need.

When a particular product is selected, my intention is to display the Operating Margin / Profit Margin in KPI management would be interested to know only this on high level, when they want to dig deep they can select from the drop down.

The problem here is, i have listed every selection under dimension of "Line Items", i don't know how to call out Operating Margin in KPI as default. If you can help me to develop this KPI that would be helpful.

Re: Sum product

Anonymous — Tue, 03 Apr 2018 10:24:17 GMT

I have worked on developing a KPI for my dashboard by using the below mentioned formula

=num(sum(aggr (sum( {<[Line Items]={"Operating Margin"},Year={"Cyc Avg"} >} [In %]*Volumes),Market))/sum(aggr (sum( {<[Line Items]={"Operating Margin"},Year={"Cyc Avg"} >} Volumes),Market)),'#,##0.0;(#,##0.0)')&'%'

However i noticed that it was not correct as i require. Instead of multiplying with Volume, i need to use "Net Revenue" which is one of the element in "line Items Grouping". Please help me to correct that formula. I am suppose to get (38.5%) as the Ops margin for KPI. I have listed the numbers below.

Market	Line Items	In $ Mils	Line Items	In %
Dubai	Net Revenue	21.16097561	Operating Margin	(36.05)
Israel	Net Revenue	0	Operating Margin	-
Northern Africa	Net Revenue	28.4195122	Operating Margin	19.61
Southern Africa	Net Revenue	95.62878456	Operating Margin	(63.01)
Other Africa	Net Revenue	21.42439024	Operating Margin	(8.79)


	(Sumproduct of Net Rev $ mils * Ops Margin %)/sum of Net Revenue)	-38.52314379

Re: Sum product

sunny_talwar — Tue, 03 Apr 2018 19:02:54 GMT

Try this

=Sum(Aggr(Sum({<[Line Item Grouping] = {'Net Revenue'}, Year = {'Cyc Avg'}>}[In $ Mils]) * sum({<[Line Item Grouping] = {'Operating Margin'}, Year = {'Cyc Avg'}>}[In %]), Market))/Sum({<[Line Item Grouping] = {'Net Revenue'}, Year = {'Cyc Avg'}>}[In $ Mils])

Re: Sum product

Anonymous — Wed, 04 Apr 2018 05:18:30 GMT

You are awesome...It absolutely worked. However, i have sent you one another request for identifying the total of all region in %. It would be nice, if you can also respond to that as well!!

Re: Sum product

sunny_talwar — Wed, 04 Apr 2018 09:03:01 GMT

Are you talking about another thread?

Re: Sum product

Anonymous — Wed, 04 Apr 2018 09:16:44 GMT

Sunny,

I dropped a personal msg to you, that is what i have referenced above. Any way, below is the msg copied from that msg.

Sunny, I tried replicating the same you did for per unit to %, but it didn't work. It will not work coz the formula for % is something different.

Generally it is sum(in %), for MEA Total it should be sum product of (Operating Margin%:Net Revenue)/Total Net revenue.

I have tried this, but not working out. Your help is needed again.

=sum({<[Line Item Grouping] = {'Operating Margin'}>}sum([In %]*sum({<[Line Item Grouping] = {'Net Revenue'}>}[In $ Mils]/Sum({<[Line Item Grouping] = {'Net Revenue'}>}[In $ Mils])

Also, is there any way that we can talk for some time? I just want to understand whether those are possible in QLIK VIEW.