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.

[mhmohammed]

Hi Ankit,

As there is a difference in Month objects in the 2 data providers, (Month Name vs Month Year), you can't merge on them and by extension, not be able to show the key figures (from 2 data providers (DP)) Monthly.

If you've a Date objects in the context from which you get the Month Name (as in DP 1), then, create a Month Year column (by formatting), use it in the DP 1, merge on Month Year columns from both DPs, and then you'll be able to show all key figures (from 2 DPs) Monthly in a report. Obviously, you'll have to make sure that the data type of this new object Month Year (in context of 1st DP) should be similar to the Month Year object (in DP2).

Makes sense?

Thanks,
Mahboob Mohammed