How to get common month from (DP1) Month and (DP2) Fiscal Period

[Former Member]

Hi,

I have 2 data providers, one has Month Name(January, February, March, April.......) and its key figures, second data provider has Fiscal Period(JAN 2016, FEB 2016, MAR 2016.......) its key figures.

We have other dimensions also where we have done the merge:

e.g.

MaterialNo., Material Type,........

So if I show dp1's and dp2's key figure on merged MaterialNo. and Material Type data comes fine.

I have a requirement to show both key figure monthly basis.

Can you please tell me the way how can I do it.

Thanks,

Ankit

[amitrathi239 · Accepted solution]

have you tried with first merge the Month & Fiscal Period objects.Later create the variable on merged object and use with measure.

Var=Upper(Substr([Merged Object];1;3))

Drag Var and  Measure objects in table and check if it fine.

[Former Member · Accepted solution]

Hi Amit,

Your solution works for getting the common month but I realized for multiple years I need to use fiscal period.

So from one dp we are getting MM/yyyy and from other dp it is MMM yyyy. I followed your first step that is to merge both fiscal period but now in variable I need to get one format of fiscal period.

I want to create a variable where I can check if format is MM/yyyy then convert it into

=FormatDate(ToDate("01/"+[Month/year]; "dd/MM/yyyy");"MMM yyyy")

else

keep second format.

Can you guide me in getting that variable.

Thanks,

Ankit

[amitrathi239 · Accepted solution]

try this.

=if(length([Month/year])=7) then FormatDate(ToDate("01/"+[Month/year]; "dd/MM/yyyy");"MMM yyyy")

else

keep second format.