topic Re: Percentage for each Cyclic Group in Separate Column in QlikView

Percentage for each Cyclic Group in Separate Column

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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%

Re: Percentage for each Cyclic Group in Separate Column

sunny_talwar — Thu, 01 Dec 2016 12:59:17 GMT

Like this?

Re: Percentage for each Cyclic Group in Separate Column

MK_QSL — Thu, 01 Dec 2016 13:04:20 GMT

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

Re: Percentage for each Cyclic Group in Separate Column

Anonymous — Thu, 01 Dec 2016 13:25:45 GMT

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

Farrukh

Re: Percentage for each Cyclic Group in Separate Column

sunny_talwar — Thu, 01 Dec 2016 13:26:24 GMT

So, did you look at the attached file?

Re: Percentage for each Cyclic Group in Separate Column

Anonymous — Thu, 01 Dec 2016 13:27:05 GMT

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

Re: Percentage for each Cyclic Group in Separate Column

MK_QSL — Thu, 01 Dec 2016 13:28:59 GMT

Sunny's reply perfectly suits to your question.

Re: Percentage for each Cyclic Group in Separate Column

Anonymous — Thu, 01 Dec 2016 14:39:43 GMT

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

Re: Percentage for each Cyclic Group in Separate Column

sunny_talwar — Thu, 01 Dec 2016 14:47:41 GMT

Re: Percentage for each Cyclic Group in Separate Column

Anonymous — Thu, 01 Dec 2016 16:13:22 GMT

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

Re: Percentage for each Cyclic Group in Separate Column

sunny_talwar — Thu, 01 Dec 2016 16:35:14 GMT

What do you mean? Can you elaborate?

Re: Percentage for each Cyclic Group in Separate Column

Anonymous — Thu, 01 Dec 2016 16:44:19 GMT

Sunny as you can see the below table it has region name in column (which is Hard Code Text or manually assigned column name).

How can I put column name for each percentage column, which will pick region for each column.

I tried the below statement to pick region as a column name but its giving me null value.

=aggr(Pick(RowNo()),$(=GetCurrentField(CH112)))

Re: Percentage for each Cyclic Group in Separate Column

sunny_talwar — Thu, 01 Dec 2016 16:47:53 GMT

Why do you not want to hard code? Can you have values other than these 4? I am trying to understand what might this to change so that I can provide you with a suitable expression

Re: Percentage for each Cyclic Group in Separate Column

Anonymous — Thu, 01 Dec 2016 16:54:12 GMT

There is a Cyclic Group, which is based on Region, District, Company, so when its rotate to District or Company then its still shows the Region which are (North West East South) instead of District and Company Name.

That's why I need to pull the cyclic group field as a column name. where the percentage has been categorized for each district or company name.

Farrukh

Re: Percentage for each Cyclic Group in Separate Column

sunny_talwar — Thu, 01 Dec 2016 18:29:40 GMT

So if you have 10 District when you cycle to district, you would want to see 10 different columns with a dynamic label?

Re: Percentage for each Cyclic Group in Separate Column

Anonymous — Thu, 01 Dec 2016 21:19:50 GMT

There are only top 3 sales, so it would be only three or four dynamic column names.

Farrukh

Re: Percentage for each Cyclic Group in Separate Column

sunny_talwar — Thu, 01 Dec 2016 21:45:26 GMT

Don't ask me how, but here you go

Re: Percentage for each Cyclic Group in Separate Column

Anonymous — Fri, 02 Dec 2016 11:47:46 GMT

Hi Sunny,

Could you please advise me the purpose of

$(vCycle)

Kind regards,

Farrukh

Re: Percentage for each Cyclic Group in Separate Column

sunny_talwar — Fri, 02 Dec 2016 11:50:40 GMT

vCycle is a variable which contains GetCurrentField() function. I was running into errors and was not able to figure it out because GetCurrentField() function is not understood by the expression editor causing issues for me to figure out what else might be wrong later in the expression. To find those errors, I saved the GetCurrentField() function in a variable and used the variable which helped me locate my other errors

Re: Percentage for each Cyclic Group in Separate Column

Anonymous — Mon, 05 Dec 2016 11:57:21 GMT

Thanks a mil Sunny,

I am nearly there.

Could you please see the attached excel sample data. there is only the issue is sorting order. currently the percentage being calculated as per RowNo.

And Column title is being display by RANKING order.

as you can see in the attached excel sample data. column title is being display correctly by RANKING order. and Region dimensions are Sorted by Sale.

Is there any possibility that Region dimension can be sorted by Ranking order too. because currently East region has 3rd top sale. and North region is 2nd. but in dimensional order East is 2nd and North is 3rd.

Kind regards,

Farrukh

Month Year	Region	Sale	South	North	East
Aug-2016	South	83,020.824	37.27%	-	-	1st Top Sale	South
Aug-2016	East	74,588.024	-	33.49%	-	2nd Top Sale	North
Aug-2016	North	65,136.000	-	-	29.24%	3rd Top Sale	East