Percentage for each Cyclic Group in Separate Column

[Anonymous]

Hi Guys,

I have a percentage of each region in on column. I am trying to have the percentage of each cyclic group in separate column.

Also I put the condition for top 3 sales of last three months in Calculated Dimension. Below is the format I am trying to put percentage for each cyclic group.

I do not want to use region name as a hard code, because its a cyclic group and field could be change to different column.

Also I attached the sample data but the expression is not working.

Could you please advise me related to resolve this issue.

Kind regards,

Farrukh

MonthYear	Region	Total Sale	West	East	South	North
		40291.599
Apr-2013	West	941.28	14.94%
Apr-2013	East	1551.1		24.63%
Apr-2013	South	1777.457			28.22%
Apr-2013	North	2028.513				32.21%
May-2013	West	941.28	14.80%
May-2013	East	1551.1		24.39%
May-2013	South	1777.457			27.95%
May-2013	North	2088.842				32.85%
Jun-2013	West	941.28	14.94%
Jun-2013	East	1551.1		24.63%
Jun-2013	South	1777.457			28.22%
Jun-2013	North	2028.513				32.21%
Jul-2013	West	941.28	11.88%
Jul-2013	East	1551.1		19.57%
Jul-2013	South	1777.457			22.43%
Jul-2013	North	3654.965				46.12%
Aug-2013	East	1551.1	21.91%
Aug-2013	West	1722.24		24.33%
Aug-2013	South	1777.457			25.11%
Aug-2013	North	2028.513				28.65%
Sep-2013	South	1030.255	16.27%
Sep-2013	East	1551.1		24.50%
Sep-2013	West	1722.24			27.20%
Sep-2013	North	2028.513				32.04%

[sunny_talwar · Accepted solution]

Like this?

[MK_QSL]

Create 4 Expressions

West

=IF(Region = 'West',

  Sum({$<Region = {'West'},Year =,Month =, YearMonth = {">=$(=Date(MonthStart(AddMonths(Min(SaleDate), -5)), 'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate), 0)), 'YYYYMM'))"}>} Sale)/Sum(TOTAL <MonthYear> {$<Year=, Month=, YearMonth = {">=$(=Date(MonthStart(AddMonths(Min(SaleDate), -5)), 'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate), 0)), 'YYYYMM'))"}>} Sale),

  Null())

East

=IF(Region = 'East',

  Sum({$<Region = {'East'},Year =,Month =, YearMonth = {">=$(=Date(MonthStart(AddMonths(Min(SaleDate), -5)), 'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate), 0)), 'YYYYMM'))"}>} Sale)/Sum(TOTAL <MonthYear> {$<Year=, Month=, YearMonth = {">=$(=Date(MonthStart(AddMonths(Min(SaleDate), -5)), 'YYYYMM'))<=$(=Date(MonthStart(AddMonths(Max(SaleDate), 0)), 'YYYYMM'))"}>} Sale),

  Null())

Same for north and south

[Anonymous]

Yes . That's the way I am trying to generate the table.

Farrukh

[sunny_talwar]

So, did you look at the attached file?

[Anonymous]

Hi Manish,

I do not want to use the region as a hard code. the problem is that there is a drilldwn/cyclic dimension, so I can not constant the region.

Farrukh

[MK_QSL]

Sunny's reply perfectly suits to your question.

[Anonymous]

Thanks a mil Sunny, its sorted, initially I didn't see the attachment.

[sunny_talwar]

[Anonymous]

You have done a great help to me.

Could you please advise me that how can I use the column name for the percentage of every column.?

I tried GetCurrentField, also tried Pick but its giving me an error.

Farrukh