topic Re: Group by Country in Expression. in QlikView

Group by Country in Expression.

infosense_devel — Wed, 05 Apr 2017 09:44:22 GMT

Hello,

we have requirement as per below,

on 2nd April for Australia we have Invoice but budget is not there so "Expected MOnth End" is 0.

and China have Budget but no invoice is there so "Expected Month End" is 0 which is correct for both case.

But when we calculate for APAC means (Australia + China) then we as per below table for 2nd April we are getting Budget and Invoice both, so it is showing "Expected Month End" 6582230. we want it as 0 because Australia and China individual 0.

Date	(Australia)				(China)
	Invoice (USD)	Budget (USD)	Full Month Budget (USD)	Expected Month End for VAUS	Invoice (USD)	Budget (USD)	Full Month Budget	Expected Month End for VCHN
1-Apr	0	0	148,176	0	0	318	6,034	0
2-Apr	13573	0	148,176	0	0	318	6,034	0

	APAC
	Invoice (USD) (VAUS+VCHN)	Budget (USD) (VAUS+VCHN)	Full Month Budget APAC(USD)	Expected Month End for APAC
1-Apr	0	318	154210	0
2-Apr	13573	318	154210	6582290.823

Expected Month end Formula is: ((full_month_budget * (100* invoiceMTD)/BudgetMTD)/100)

and We are having Country dimension.

Re: Group by Country in Expression.

MK_QSL — Wed, 05 Apr 2017 09:46:43 GMT

Provide sample data or sample app for your this query..

Re: Group by Country in Expression.

johanlindell — Wed, 05 Apr 2017 09:56:31 GMT

Try Sum (Aggr(((full_month_budget * (100* invoiceMTD)/BudgetMTD)/100), Country))

Re: Group by Country in Expression.

infosense_devel — Thu, 06 Apr 2017 08:57:42 GMT

Hello,

Thanks for your reply, I have tried with Aggr formula as you have provided but it is giving wrong result.

Re: Group by Country in Expression.

johanlindell — Thu, 06 Apr 2017 09:29:13 GMT

Well, if you supply a QlikView document with some example data showing the problem would be helpful. I tried to make a simple data model to show the problem and a solution, but the data you have given is insufficent to make an example. Preferrable with some inline data loads.

/Johan