How to group column names as a new dimension?

[josephinetedesc]

Hi

I have a number of columns in a flat file.

For example: [Financial Year],[Financial Month], Company, Division,

[January Net Amount], [January YTD Amount], [January Bud Amount],

[February Net Amount], [February YTD Amount], [February Bud Amount] etc for every month.

How do I put in a dimension called January which will give me just those 3 January columns, another called February which will give me just those February columns?

Jo

[Anil_Babu_Samineni]

Some thing like below?

Fact:

LOAD [Financial Year],[Financial Month], Company, Division,

[January Net Amount], [January YTD Amount], [January Bud Amount], 'January' as Flag

From Fact;

Concatenate (Fact)

LOAD [Financial Year],[Financial Month], Company, Division,

[February Net Amount], [February YTD Amount], [February Bud Amount], 'February' as Flag

from Fact;

Concatenate (Fact)

LOAD [Financial Year],[Financial Month], Company, Division,

[March Net Amount], [March YTD Amount], [March Bud Amount], 'February' as Flag

from Fact;

.............

[Anonymous]

Hi

You can Use Cyclic group or Drill down

Chart Properties --> Dimension --> Edit Group --> New Group -->

Select the Dimension --> OK

New Group will be Created.

Name the Group According to your Requirement.

And Add the Dimension which you want in the Group

Regards

Tahemas Momin

[josephinetedesc]

thank you that works - but ...

how do I arrange it that when the Flag January is selected that the 4 tables are selected?  I suppose set analysis based on Flag = January?

[shraddha_g]

Load [Financial Year],[Financial Month], Company, Division,

[January Net Amount] as [Net Amount],

[January YTD Amount] as [YTD Amount],

[January Bud Amount] as [Bud Amount]

'January' as Month

From Table;

concatenate

Load [Financial Year],[Financial Month], Company, Division,

[February Net Amount] as [Net Amount],

[February YTD Amount] as [YTD Amount],

[February Bud Amount] as [Bud Amount]

'February' as Month

From Table;

You can do this for all 12 Month

In this, For dimension Month , When you select January, all 3 Measures will return data for [January Net Amount], [January YTD Amount], [January Bud Amount].

[Anil_Babu_Samineni]

Off course?

May be use some thing like below

Pick(Match(Flag, 'January','February','March',...), Sum({<Flag = {'January'}>}Sales), Sum({<Flag = {'February'}>}Sales), Sum({<Flag = {'March'}>}Sales), ....)

The same way, If Customer / USER needs only for January Sales, You could use something like below

Sum({<Flag = {'January'}>}Sales)

[josephinetedesc]

Hi Momin

I am not sure how to use the Drill down group because to make the group 'January' I have to include the 4 columns.  So I would need to create a super column called January - not sure how to do that.

Jo

[josephinetedesc]

Yes this works - thank you

Jo

[its_anandrjs]

I suggest you have to try this

Fact:

Load

[Financial Year],[Financial Month], Company, Division,

[January Net Amount] as [Net Amt],

[January YTD Amount] as [YTD Amt],

[January Bud Amount] as [Bud Amt]

From Source

Where [Financial Month] = 'January Month Selected Here'; // this is for all January Month for all Financial years

Concatenate(Fact)

Load

[Financial Year],[Financial Month], Company, Division,

[February Net Amount] as Net Amt],

[February YTD Amount] as [YTD Amt],

[February Bud Amount] as [Bud Amt]

From Source

Where [Financial Month] = 'February Month Selected Here'; // this is for all February Month for all Financial years

...... Do this for all months and after this you get for all months data in Fact table.

[its_anandrjs · Accepted solution]

Also please add a flag field over the table also

Ex:-

Fact:

Load

[Financial Year],[Financial Month], Company, Division,

[January Net Amount] as [Net Amt],

[January YTD Amount] as [YTD Amt],

[January Bud Amount] as [Bud Amt],

'Jan' as MonthSelection

From Source

Where [Financial Month] = 'January Month Selected Here'; // this is for all January Month for all Financial years

Concatenate(Fact)

Load

[Financial Year],[Financial Month], Company, Division,

[February Net Amount] as Net Amt],

[February YTD Amount] as [YTD Amt],

[February Bud Amount] as [Bud Amt],

'Feb' as MonthSelection

From Source

Where [Financial Month] = 'February Month Selected Here'; // this is for all February Month for all Financial years