Introducing the all-new TIBCO Community site!

For current users, please click "Sign In" to reset your password and access the enhanced features. If you're a first-time visitor, we extend a warm welcome—click "Sign Up" to become a part of the TIBCO Community!

If you're seeking alternative community sites, explore ibi, Jaspersoft, and Spotfire.

Jump to content

Average of sum in KPI visualization for days with complete data


Thorsten Eiben

Recommended Posts

I have the following data:

 

 

 

Date

A

B

C

 

 

 

 

Jan-1

100

 

 

 

 

 

Jan-1

 

200

 

 

 

 

Jan-1

 

 

50

 

 

 

Jan-2

75

 

 

 

 

 

Jan-2

 

50

 

 

 

 

Jan-3

110

 

 

 

 

 

Jan-3

 

30

 

 

 

 

Jan-3

 

 

100

 

 

 

 

Imagine that these arethree stores with sales data. I want to display the average of sales on those days where reports from all three stores have been received.

So I want to calculate the average of the sums for the days where I have complete data, i.e. the average for the sum on Jan-1 (350) and Jan-3 (240). Jan-2 should be ignored as the data is not yet complete. For this simple example the result shown in the KPI should be 295.

Can anyone point me into the right direction

Link to comment
Share on other sites

Assuming you have a fixed number of stores and column names, try this:

First collapse the sales amounts [A], and [C]into [ABC] calculated as:

Sum([A],,[C])

Then calculate your sum as:

If(Count([Date]) OVER ([Date])=3,Sum([ABC]) over ([Date]),Null)

If your number of stores is variable, you could still calculate the Sum([A],,C],..) manually, but an idea could be to select the columns you want to consider as stores into a document property first and automate this step (and also modify the 3 into the number of stores).

Check out this answer:

https://stackoverflow.com/questions/62400028/how-do-i-create-an-interact...

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...