Sum product

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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)

[tripatirao]

Hi Sunny,

why you used Dimensionality() here.

Please let me know

[sunny_talwar]

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?

[Anonymous]

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.

[Anonymous]

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

[sunny_talwar]

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

[Anonymous]

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

[sunny_talwar]

Are you talking about another thread?

[Anonymous]

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.