Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
dhasharadh
Creator III
Creator III

Avg Base calculation

Can any one suggest the best way to get the below. I have an year Month field (YYYYMM) in the pivot table and I have to show the Avg Base, below is the sample data. I want to calculate avg Base( last column)

Brand Date Base Avg Base formula Avg Base
AA 01.01.2022 500 (0+500)/2 255
AA 01.02.2022 600 (500+600)/2 550
AA 01.03.2022 700 (600+700)/2 650
AA 01.04.2022 800 (700+800)/2 750
AA 01.05.2022 900 (500+600)/2 850
Labels (1)
2 Solutions

Accepted Solutions
Andrei_Cusnir
Specialist
Specialist

Hello,

 

There might be some mistakes with the calculations that you have provided so I wanted to share with you here just in case there is an issue. 

 

Based on the column Base, the first record is 100 because (0+200-100). The 0 is 0, because before that record, there are no any details, so we assume that Null - Null = 0, hence Null - Null + 200 - 100 is 100.

 

For the 2nd record I can see correct value: 200 - 100 (Previous Base) + 300 - 50 (Current Base), hence 100 + 250 = 350. For the next record though we have 300 - 50 + 400 - 200 = 250 + 200 = 450, but you have specified 550, which I assume it is a typo.

 

For this column the expression is: =If(IsNull(Above(TOTAL Deletes)), 0, Above(TOTAL Adds) - Above(TOTAL Deletes)) + Adds - Deletes

 

Now for the final column, I can see that for the first record you have 0+100/2 which is (as you mentioned) Previous Base + Current Base / 2. Therefore, 0 because previous base is Null. However for the second part, previous base is 100 and current base/2 is 350/2, so  175. So the outcome should be 175 + 100 which is 275, but you have specified 225. I assume that this is also a typo. The reason I am stating this, is because I have implemented the expressions based on the requirements, but the numbers doesn't match the representation that you gave. For the final column this is the expression that I have used: =If(IsNull(Above(TOTAL Column(1))), 0, Above(TOTAL Column(1))) + (Column(1) / 2)

 

The outcome is:

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂

View solution in original post

Andrei_Cusnir
Specialist
Specialist

Hello,

 

Since you are doing those calculations in real time, they are affected based on the selections that you make. However, as far as I understand, the calculated Base and Avg of Base is calculated for the specific date, so you wouldn't like it to change, based on the selection, but rather show the same number when you limit the rows on the view.

 

There are 2 suggestions for this use case scenario:

1. Either generate the values of Base and Avg of Base during reload in Data load editor

(This will require some modifications and entirely different logic probably, as Above() and Before() functions, doesn't work in script)

or

2. Create a new state and apply this state to the Table chart or Pivot Table chart, so the selections you make, won't affect the rows of the table.

 

However, if you would need further assistance with any of the above suggestions, I would recommend creating a new post, because the initial issue is already resolved: "How to calculate the Base and Avg of Base", now you have an issue with the Null, which should be addressed in a different post, to avoid making the solution super complicated.

 

I hope that this information helps.

 

 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂

View solution in original post

8 Replies
hic
Former Employee
Former Employee

I assume that your dimensions are Brand and Date, so that Base really is an aggregation, like Sum(Base). Then you can use the following to get the average of the two rows:

RangeSum(Sum(Base),Above(Sum(Base)))/2

Andrei_Cusnir
Specialist
Specialist

Hello,

 

You can try using the expression:

=(Base + If(IsNull(Above(TOTAL Base)), 0, Above(TOTAL Base)))/2

 

Here is an outcome on Straight table and Pivot table. As you can see it correlates with what you mentioned in your custom table.

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
dhasharadh
Creator III
Creator III
Author

Many Thanks @Andrei_Cusnir 

Small change in my original requirement. Would be really helpful if you provide exp for the below please.

Consider before 202201 Base as 0.

Base = (Previous Month Base) + Adds - Delete

Avg of Base = (Previous Month Base + Curent Month Base)/2

Brand Date Adds Deletes Base Avg of Base
AA 01.01.2022 200 100 100 (0+200-100) (100 + 0)/2 = 50
AA 01.02.2022 300 50 350 225
AA 01.03.2022 400 200 550 450
AA 01.04.2022 300 150 700 625
AA 01.05.2022 350 200 850 775
dhasharadh
Creator III
Creator III
Author

Many Thanks for the reply @hic you are really an inspiration, I love your articles on different topics in Qlik area.

dhasharadh
Creator III
Creator III
Author

Many Thanks @Andrei_Cusnir 

Just now I have understood my requirement, it will really helpful if you provide expressions for the below

Consider before 202201 Base as 0.

Base = (Previous Month Base) + Adds - Delete

Avg of Base = (Previous Month Base + Curent Month Base)/2

Brand Date Adds Deletes Base Avg of Base
AA 01.01.2022 200 100 100 (0+200-100) 50 (0+100/2)
AA 01.02.2022 300 50 350 225
AA 01.03.2022 400 200 550 450
AA 01.04.2022 300 150 700 625
AA 01.05.2022 350 200 850 775
Andrei_Cusnir
Specialist
Specialist

Hello,

 

There might be some mistakes with the calculations that you have provided so I wanted to share with you here just in case there is an issue. 

 

Based on the column Base, the first record is 100 because (0+200-100). The 0 is 0, because before that record, there are no any details, so we assume that Null - Null = 0, hence Null - Null + 200 - 100 is 100.

 

For the 2nd record I can see correct value: 200 - 100 (Previous Base) + 300 - 50 (Current Base), hence 100 + 250 = 350. For the next record though we have 300 - 50 + 400 - 200 = 250 + 200 = 450, but you have specified 550, which I assume it is a typo.

 

For this column the expression is: =If(IsNull(Above(TOTAL Deletes)), 0, Above(TOTAL Adds) - Above(TOTAL Deletes)) + Adds - Deletes

 

Now for the final column, I can see that for the first record you have 0+100/2 which is (as you mentioned) Previous Base + Current Base / 2. Therefore, 0 because previous base is Null. However for the second part, previous base is 100 and current base/2 is 350/2, so  175. So the outcome should be 175 + 100 which is 275, but you have specified 225. I assume that this is also a typo. The reason I am stating this, is because I have implemented the expressions based on the requirements, but the numbers doesn't match the representation that you gave. For the final column this is the expression that I have used: =If(IsNull(Above(TOTAL Column(1))), 0, Above(TOTAL Column(1))) + (Column(1) / 2)

 

The outcome is:

 

I hope that this information was helpful. In case I have misunderstood the use case scenario, please elaborate in details by providing additional information. However, if it has helped you resolve the issue, addressed your concerns or at least pointed you in the right direction, please mark it as Accepted Solution to give further visibility to other community members. 
 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂
dhasharadh
Creator III
Creator III
Author

Many Thanks @Andrei_Cusnir  for your time on this, 

I am almost sorted, but this Above  working in Straight table and I replace it with Before in Pivot it also working. 

Only concern here when we select any particular Month its showing nulls (the reason could be our logic of Above or Before works fine when we have some rows Above or Before, Correct me here i am wrong)

Is there any way using some variables in set expression where we can show 202201 = Base of 202112 + Adds -Delete. ??

 

Many thanks again.

Andrei_Cusnir
Specialist
Specialist

Hello,

 

Since you are doing those calculations in real time, they are affected based on the selections that you make. However, as far as I understand, the calculated Base and Avg of Base is calculated for the specific date, so you wouldn't like it to change, based on the selection, but rather show the same number when you limit the rows on the view.

 

There are 2 suggestions for this use case scenario:

1. Either generate the values of Base and Avg of Base during reload in Data load editor

(This will require some modifications and entirely different logic probably, as Above() and Before() functions, doesn't work in script)

or

2. Create a new state and apply this state to the Table chart or Pivot Table chart, so the selections you make, won't affect the rows of the table.

 

However, if you would need further assistance with any of the above suggestions, I would recommend creating a new post, because the initial issue is already resolved: "How to calculate the Base and Avg of Base", now you have an issue with the Null, which should be addressed in a different post, to avoid making the solution super complicated.

 

I hope that this information helps.

 

 

Help users find answers! Don't forget to mark a solution that worked for you! 🙂