Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
farrukh_shaikh
Creator II
Creator II

Percentage for each Cyclic Group in Separate Column

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

   

MonthYearRegionTotal SaleWestEastSouthNorth
40291.599
Apr-2013West941.2814.94%
Apr-2013East1551.1 24.63%
Apr-2013South1777.457 28.22%
Apr-2013North2028.513 32.21%
May-2013West941.2814.80%
May-2013East1551.1 24.39%
May-2013South1777.457 27.95%
May-2013North2088.842 32.85%
Jun-2013West941.2814.94%
Jun-2013East1551.1 24.63%
Jun-2013South1777.457 28.22%
Jun-2013North2028.513 32.21%
Jul-2013West941.2811.88%
Jul-2013East1551.1 19.57%
Jul-2013South1777.457 22.43%
Jul-2013North3654.965 46.12%
Aug-2013East1551.121.91%
Aug-2013West1722.24 24.33%
Aug-2013South1777.457 25.11%
Aug-2013North2028.513 28.65%
Sep-2013South1030.25516.27%
Sep-2013East1551.1 24.50%
Sep-2013West1722.24 27.20%
Sep-2013North2028.513 32.04%
1 Solution

Accepted Solutions
sunny_talwar

35 Replies
sunny_talwar

MK_QSL
MVP
MVP

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

farrukh_shaikh
Creator II
Creator II
Author

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

Farrukh

sunny_talwar

So, did you look at the attached file?

farrukh_shaikh
Creator II
Creator II
Author

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
MVP
MVP

Sunny's reply perfectly suits to your question.

farrukh_shaikh
Creator II
Creator II
Author

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

sunny_talwar

farrukh_shaikh
Creator II
Creator II
Author

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