topic Re: Avg Base calculation in App Development

Avg Base calculation

dhasharadh — Wed, 06 Jul 2022 06:35:59 GMT

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

Re: Avg Base calculation

hic — Wed, 06 Jul 2022 07:37:20 GMT

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

Re: Avg Base calculation

Andrei_Cusnir — Wed, 06 Jul 2022 07:56:57 GMT

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.

Re: Avg Base calculation

dhasharadh — Wed, 06 Jul 2022 19:54:19 GMT

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

Re: Avg Base calculation

dhasharadh — Wed, 06 Jul 2022 19:57:12 GMT

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

Re: Avg Base calculation

dhasharadh — Wed, 06 Jul 2022 21:32:27 GMT

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

Re: Avg Base calculation

Andrei_Cusnir — Thu, 07 Jul 2022 07:16:36 GMT

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:

Re: Avg Base calculation

dhasharadh — Thu, 07 Jul 2022 09:34:17 GMT

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.

Re: Avg Base calculation

Andrei_Cusnir — Tue, 12 Jul 2022 07:37:18 GMT

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)

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.