Display data for last 12 months in a chart

[Former Member]

Hello community,

I'm facing an issue concerning the display of data in a chart, for the last 12 months.

I have 3 KPI to display : KPI Y, KPY Y-1 and KPY Y-2.

Each KPI has a fiscal/year period restriction :

• Period/Fiscal Year (Interval Entry required) for KPI Y
• Period/Fiscal Year (Interval Entry required) - 52 for KPI Y-1
• Period/Fiscal Year (Interval Entry required) - 104 for KPI Y-2

My goal : Display the 3 KPI in a same chart, with the same X-axis (Posting Period / Week Number)

My issue :

My selected period is : 014.2015 to 013.2016

And my chart use posting period (1 to 52).

So in this case and in my chart, for my KPI Y, Posting period 1 to 13 represents data from 2016. Posting period 14 to 52 represents data from 2015.

So 2016 data are represented before 2015 data...

The need : Posting period 2015 display before 2016 (so X-Axis like 14 15 16.... 52 1 2 3 4.... 13)

I tried different solution (unsuccessfully), and we can't use Year dimension for this chart. If we use it, we will have data as 01.2014, 02.2014... 01?2015... 13.2016 and we won't have our 3 KPI in a same space, using the same posting period.

Thank all for your help.

Best regards,

Jonas.

[MustafaBensan]

Hi Jonas,

If I understand correctly, your period represents a week of the year but you appear to be using an x-axis dimension that has Period/Fiscal Year compounded.  Do you have a Period dimension that is based only on the week number (1-52)?  This is the only way you will be able to superimpose the three KPI measures on the same chart.

It looks like your data source is BEx, so in that case, I would suggest a query structure as follows:

1)  KPY Y    Key Figure restricted to Current Fiscal Year (YYYY) Variable

2)  KPY Y-1 Key Figure restricted to Current Fiscal Year (YYYY) Variable - 1

3)  KPY Y-2 Key Figure restricted to Current Fiscal Year (YYYY) Variable - 2

4)  Chart X dimension specified as Period representing week only (1 -52) without Fiscal Year compounded

Regards,

Mustafa.

[Former Member]

Hello Mustafa,

Posting Period is a dimension based only on the week number, that why i use it for my chart.

And unfortunately, i can't create my KPI restricted to the current fiscal year, I want to have a KPI which can have data for the last 52 weeks, that why i used fiscal year/period as variable restriction.

The only way i found to achieve this requirement is to be able to re-order posting period.

If my period selection go from 015.2015 to 014.2016, i want to have 15 as first posting period in my chart.

We tried to achieve this by using a KPI with Replacement Path, unsuccessfully for now.

[Former Member]

Hi Jonas!

Could you try a custom sort? I have the feeling that you should be able to sort the data in a way that allows this to happen correctly.

For example, if you add year to the Query but only choose to show periods, can you sort on the year dimension and get the intended result?

Good luck

[Former Member]

Hi Emil,

I tried different solution, add year dimension as rows not displayed, add year dimension as background filter... and sort this dimension, but it didn't work, i always have my week in order ascending.

But you are right, if i'm able to sort on a custom dimension or measure which represents the year, it can be the solution. That why we tried to create a Key Figure with Replacement Path based on Fiscal Year, but it didn't work

[Former Member]

Hi Jonas,

Why not add the year only dimension before the week only dimension and show it too?

That way it will sort as well as show the year to which the weeks belong to. Something like this.

If you do not want to show year then like Emil said you can set the Member Display property of year only dimension to No Display and just display the week numbers.

Regards,

Swapnil Koti

[Former Member]

Hello Swapnil,

As i explain, i dont want to separate each year. The aim is to have the 3 KPI (Y, Y-1 and Y-2) in the same chart. If i add the Year, i won't have this requirement.

I tried to add it in my datasource with "No Display", unsuccessfully , in all case, my posting period is sorted from 1 to 52