Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mahendrankr
Contributor III
Contributor III

Calculate Sum Basis Data Availability for another Dimension

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.

Labels (1)
2 Replies
Or
MVP
MVP

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.

Or_0-1639661613990.png

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.

 

mahendrankr
Contributor III
Contributor III
Author

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.