Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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% |
Like this?
Like this?
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
Yes . That's the way I am trying to generate the table.
Farrukh
So, did you look at the attached file?
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
Sunny's reply perfectly suits to your question.
Thanks a mil Sunny, its sorted, initially I didn't see the attachment.
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