topic Calculate Sum Basis Data Availability for another Dimension in App Development

Calculate Sum Basis Data Availability for another Dimension

mahendrankr — Thu, 16 Dec 2021 12:27:40 GMT

Hi Team,

I have two tables .

Table 1) Sales Value of two products available for different period (Clean X untill Jun and Battery untill Apr).

Table 2) Has the cost for both these products but available untill Dec .

Question is how do i calcuate the Profit (Sales-Cost) for the period only untill sales value is available for each product.

Sample Table below:

Month	Country	Products	Sales	Month	Country	Products	Cost
Jan	UKI	Clean X	10,959,685	Jan	UKI	Clean X	10,959,685
Feb	UKI	Clean X	5,672,042	Feb	UKI	Clean X	5,672,042
Mar	UKI	Clean X	7,903,795	Mar	UKI	Clean X	7,903,795
Apr	UKI	Clean X	4,061,328	Apr	UKI	Clean X	4,061,328
May	UKI	Clean X	6,208,527	May	UKI	Clean X	6,208,527
Jun	UKI	Clean X	3,067,256	Jun	UKI	Clean X	3,067,256
Jan	USA	Battery	10,959,685	Jul	UKI	Clean X	10,959,685
Feb	USA	Battery	5,672,042	Aug	UKI	Clean X	5,672,042
Mar	USA	Battery	7,903,795	Sept	UKI	Clean X	7,903,795
Apr	USA	Battery	4,061,328	Oct	UKI	Clean X	4,061,328
				Nov	UKI	Clean X	3,067,256
				Dec	UKI	Clean X	10,959,685
				Jan	UKI	Battery	10,959,685
				Feb	UKI	Battery	5,672,042
				Mar	UKI	Battery	7,903,795
				Apr	UKI	Battery	4,061,328
				May	UKI	Battery	6,208,527
				Jun	UKI	Battery	3,067,256
				Jul	UKI	Battery	10,959,685
				Aug	UKI	Battery	5,672,042
				Sept	UKI	Battery	7,903,795
				Oct	UKI	Battery	4,061,328
				Nov	UKI	Battery	3,067,256
				Dec	UKI	Battery	10,959,685

Thanks in advance.

Re: Calculate Sum Basis Data Availability for another Dimension

Or — Thu, 16 Dec 2021 13:36:19 GMT

If these are two separate data tables, and the identical-name fields function as keys, and there is always a value for every month up until the last one (so, if the last month is November, May can't be missing), you could get away with just a simple sum:

Sum(Sales - Cost)

This is because any time the Sales part of the equation is missing (null), then Sales - Cost is null. I've demonstrated using a partial, simplified dataset below - note that the 1000 cost for Battery in month 3 does not factor into the result on the left.

If your data doesn't match this description and this was an oversimplified example, please elaborate on the actual structure as another solution may be required.

Re: Calculate Sum Basis Data Availability for another Dimension

mahendrankr — Fri, 17 Dec 2021 02:51:34 GMT

I am computing the profit in a KPI box. So by default without any filters,

"the sum of sales" - "sum of cost only untill the period sales is available" is what i am looking for. And this may vary from product to product. therefore in the kpi box, the cost shoudl be taken only for those periods for which the sales is available for each product. i hope i am elaborative now.