
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


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....

3. Re: Build a table with different calculation per row
Shinichiro Murakami Oct 30, 2017 8:33 AM (in response to Kevin Esther)
Dummy Data_KPI_SM_10.3_V2.twbx 23.3 KB


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

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

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

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