topic Re: Logic Help? in QlikView

Logic Help?

rohitians — Mon, 30 Jun 2014 12:53:31 GMT

Hello,

I have a scenario where i need to calculate contribution of product to division and division to company

Current:

Division	Product Group	2014-15	Contr %	Required Contr %
		11,749	100.00
INDOCO	Total	4,260	36.26	36.26
INDOCO	CYCLOPAM TAB	760	6.47	17.84
INDOCO	CYCLOPAM SUS	351	2.99	8.23
INDOCO	CLOBEN G - 15 GMS	269	2.29	6.31
INDOCO	MCBM 69 10S	240	2.04	5.63
INDOCO	VEPAN 500 TAB 10S	174	1.48	4.08

Help!!

Re: Logic Help?

sasikanth — Mon, 30 Jun 2014 13:09:41 GMT

HI rohit

can you post an your sample file so that it will be easy to understand

Re: Logic Help?

rwunderlich — Mon, 30 Jun 2014 13:20:36 GMT

Contribution of Product to Division:

=sum(Sales) / sum(TOTAL <Division> Sales)

Contribution to Company:

=sum(Sales) / sum(TOTAL)

-Rob

Re: Logic Help?

rohitians — Tue, 01 Jul 2014 08:04:58 GMT

Dear Rob,

Both calculation have to be done in same column..

Thanks

Re: Logic Help?

Anonymous — Tue, 01 Jul 2014 08:11:34 GMT

Hi,

You may enable "Show Partial Sums" option available in presentation tab for Division and Product to achieve that.

Re: Logic Help?

rwunderlich — Tue, 01 Jul 2014 12:40:48 GMT

I don't quite understand your comment about both calcs being in the same column. You mentioned two calcs in your post, which one do you want in the column? If you want as displayed in your post (pct of all), then just check the "relative: property for the expression or use the Sum(Sales) / Sum(TOTAL Sales)

-Rob

Re: Logic Help?

rohitians — Thu, 03 Jul 2014 06:11:46 GMT

Dear Rob,

Following is code,

=(((SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},MonthName=,Quarter=

,[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear),0,4)}>}[Posting Date])))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}

>}[Gross Sales Amount])

-SUM({$<FinancialYear=,FiscalYear={$(=max(FiscalYear))},MonthName=,Quarter=

,[Posting Date]={">=$(=YearStart(max({$<FiscalYear={$(=max(FiscalYear),0,4)}>}[Posting Date])))<=$(=max({$<FiscalYear={$(=max(FiscalYear))}>}[Posting Date]))"}

>}[Sales Return Amount])

)/10000000)/vPNSValue)*100

Were vPNSValue contains total PNS value for current year

The report has two dimension division and products..... on division level it shows proper contribution but on product level also it shows contribution to the whole i want the products to show contribution to the divison rather then vPNSValue

Division	Product Group	2014-15	Contr%-Tot PNS	Required Contr%-Tot PNS
		12,667	100.00%	100.00%
INDOCO	Total	4,586	36.21%	36.21%
INDOCO	CYCLOPAM TAB	830	6.55%	18.1
INDOCO	CYCLOPAM SUS	390	3.08%	47.0
INDOCO	CLOBEN G - 15 GMS	310	2.45%	79.6
INDOCO	MCBM 69 10S	259	2.05%	83.6
INDOCO	VEPAN 500 TAB 10S	184	1.45%	70.9

Thanks Rob.