Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Re: Avg Base calculation

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

dhasharadh

Creator III

2022-07-05
07:51 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 |

1,158 Views

2 Solutions

Accepted Solutions

Andrei_Cusnir

Specialist

2022-07-07
03:13 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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! 🙂

Andrei_Cusnir

Specialist

2022-07-12
03:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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! 🙂

8 Replies

hic

Former Employee

2022-07-06
03:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2022-07-06
03:56 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2022-07-06
03:54 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 |

1,054 Views

dhasharadh

Creator III

2022-07-06
03:57 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,101 Views

dhasharadh

Creator III

2022-07-06
05:32 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 |

1,096 Views

Andrei_Cusnir

Specialist

2022-07-07
03:13 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

2022-07-07
05:34 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,067 Views

Andrei_Cusnir

Specialist

2022-07-12
03:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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! 🙂

Community Browser