In a BICS environment where the Bex Query is taken as a source for creating Webi, sometimes we face a challenge in sorting(Ascending/Descending) using Fiscal Year Period – Text.
Here am trying to explain some steps which we need to do, so that the soring issue can be avoided in Webi.
We face this issue mainly when we want to show the analysis of a period(say 6 months) and a change in year happens within the range of analysis.
Below Screenshots shows the Ascending and Descending behaviour, when we drag the Fiscal Year Period text and a measure(in this case, CRM Lead Qty)
In Such Scenarios, request the BW Team to add 0FISCYEAR also in the same query along with 0FISCPER.
Then try creating the corresponding Webi again and ensure that you drag in the Fiscal Year also to the query panel along with other dimensions and measures.(See Below Screenshot). You can also notice that the there is a change in Fiscal year in the data which we have taken for analysis.
Now do the required sorting first on Fiscal Year and then on the fiscal year period-Key(as shown in below screenshots)
Now you can see that the Fiscal Year Period – Text is properly sorted.
In case, if you really don’t want Fiscal Year and Fiscal Year/Period – Key for your further analysis, Hide them as shown below:
Now we are able to show the Fiscal Year/Period in a sorted order.
You can also explore the following option in Bex side for meeting the same requirement.
PS: The same steps are applicable for 0CALYEAR and 0CALMONTH combination scenarios as-well.
Hope, this is helpful!
Thanks and Regards