7 Replies Latest reply on Oct 30, 2017 11:22 AM by Shinichiro Murakami

Build a table with different calculation per row

Kevin Esther Oct 30, 2017 7:08 AM

Hi,

I am trying to build a table that shows YTD, This month etc........ for several KPI's

Some of the rows I will be using sum, then average and also dividing one KPI by another.

I have managed to sum based on parameter date and one KPI just not clear how to create the other calculations.

I have attached a sample data set.

Kev

Dummy Data.twbx 22.8 KB

1. Re: Build a table with different calculation per row

Shinichiro Murakami Oct 30, 2017 7:18 AM (in response to Kevin Esther)
Hi Kevin

I am not sure I understnd your question correctly or not, anywasy

Adding else 0 will eliminate 'nulls' and you can remove KPI from the table.

Thanks,
shin

Dummy Data_KPI_SM_10.3.twbx 17.4 KB
Like Show 0 Likes(0)

Actions
2. Re: Build a table with different calculation per row

Kevin Esther Oct 30, 2017 7:46 AM (in response to Shinichiro Murakami)

Hi Shin,

Thanks for responding am probably just not being clear I need to keep the KPI's.

I want to be able to carry out different calculations based on the KPI column.

For example

I want to be able to check for KPI then do a calculations based on this.

for example in the same calculated field.
IF [KPI] = 'KPI 1 Average' Then Average if dates match etc........
Elseif
IF [KPI] = "KPI 2 Sum' Then Sum ifs dates match etc....
Like Show 0 Likes(0)

Actions
3. Re: Build a table with different calculation per row

Shinichiro Murakami Oct 30, 2017 8:33 AM (in response to Kevin Esther)
Hi Kevin,

Just as two example of 2 KPIs

Thanks,
Shin

Dummy Data_KPI_SM_10.3_V2.twbx 23.3 KB

1 of 1 people found this helpful
Like Show 0 Likes(0)

Actions
4. Re: Build a table with different calculation per row

Kevin Esther Oct 30, 2017 9:17 AM (in response to Shinichiro Murakami)

Thanks Shin,

This makes more sense to me now.

Do you know why the average function showing a different result to when I use the sum function divided by the number of months?

Thanks for your help today
Like Show 0 Likes(0)

Actions
5. Re: Build a table with different calculation per row

Shinichiro Murakami Oct 30, 2017 9:25 AM (in response to Kevin Esther)

Hi Kevin,

Ned further investigation but ,I guess that is based on granularity level.
Avg is working on line item basis, and if you want to calc avg of (sum of month), formula should be sum of month / number of month.
If your expected result is sum / count of months, you'd better use it.

Thanks,
Shin
Like Show 0 Likes(0)

Actions
6. Re: Build a table with different calculation per row

Kevin Esther Oct 30, 2017 9:33 AM (in response to Shinichiro Murakami)

Hi Shin,

I used datepart with my parameter date work fine.

It looks like the avg function dividing by the full count which is 21 no matter how many values/months

I use the datepart function just now but be interested to know if you can use the avg function.

Thanks again.
Kev
Like Show 0 Likes(0)

Actions
7. Re: Build a table with different calculation per row

Shinichiro Murakami Oct 30, 2017 11:22 AM (in response to Kevin Esther)

You can use "avg" function I believe, but that requires much much more complicated formula in each calculated field.

The reason is LOD calculation is captureing all the period of last year months + this year 9 month =21.
Limiting those period to only 9 month with LOD formula is quite complicated. (Not impossible, but not worth spending time for me)

Thanks,
Shin
Like Show 0 Likes(0)

Actions