topic Re: Re: Re: Combining Dimension Values in a Single Pivot Cell in QlikView

Combining Dimension Values in a Single Pivot Cell

Mon, 09 Dec 2013 16:33:24 GMT

Hi,

I need to to combine two values in a single pivot cell to show the following :

	Apr	May	Jun	Jul	Aug	Sep
Incomes	4 (7)	8	7	11 (11)	5	2 (4)

The data that supplies this pivot would be set out in the following way :

Income	Apr	4
Income_2	Apr	7
Income	May	8
Income	Jun	7
Income	Jul	11
Income_2	Jul	11
Income	Aug	5
Income	Sep	2
Income_2	Sep	4

So What I basically need to test for is if there is an Income_2 for a month then show it in Brackets in the same cell for the same month.

Many thanks for any help!!

Tom

Re: Combining Dimension Values in a Single Pivot Cell

jerem1234 — Mon, 09 Dec 2013 17:16:45 GMT

Please find attached. If your data model is a little different, then you might have to change up the expression. Used ='Incomes' as a dimension along with Month. Then for the expression I used:

if(count(Income)=1, Income, concat(Income, ' (') & ')')

Hope this helps!

Re: Combining Dimension Values in a Single Pivot Cell

Tue, 10 Dec 2013 09:59:04 GMT

Excellent - many thanks for your help!

Re: Combining Dimension Values in a Single Pivot Cell

Tue, 10 Dec 2013 10:46:39 GMT

Hope you dont mind me asking another question? If, more indicators are added to the data in future is it possibly to amend the expression based on an Indicator_ID ? eg :

Ind_No	Indicator	Month	Number
1	Income	Apr	4
2	Income_2	Apr	7
1	Income	May	8
1	Income	Jun	7
1	Income	Jul	11
2	Income_2	Jul	11
1	Income	Aug	5
1	Income	Sep	2
2	Income_2	Sep	4
3	Sales	Apr	45
3	Sales	May	32
3	Sales	Jun	22
3	Sales	Jul	56
3	Sales	Aug	24
3	Sales	Sep	65

to show

Indicator	Apr	May	Jun	Jul	Aug	Sep
Income	4 (7)	8	7	11 (11)	5	2 (4)
Sales	45	32	22	56	24	65
Grand Total	56	40	29	78	29	71

Many thanks again for your help!

Tom

Re: Re: Combining Dimension Values in a Single Pivot Cell

jerem1234 — Tue, 10 Dec 2013 14:55:49 GMT

Added the extra requirements, please find attached.

Hope this helps!

Re: Combining Dimension Values in a Single Pivot Cell

Tue, 10 Dec 2013 15:08:45 GMT

Outstanding - much appreciated!

Tom

Re: Re: Re: Combining Dimension Values in a Single Pivot Cell

Thu, 09 Jan 2014 12:16:08 GMT

Hi again,

This solution was working perfectly until the requirement was changed. The source data now has many more indicators, so rather then combine values for Indicators 1 and 2, I have added a concat_flag field to the data.

I've attached a sample doc based on your original solution with test data loaded and an example of what I need to show.

Many thanks for your time.

Regards,

Tom

Re: Re: Re: Re: Combining Dimension Values in a Single Pivot Cell

jerem1234 — Thu, 09 Jan 2014 18:49:38 GMT

Please find attached.

Hope this helps!

Re: Combining Dimension Values in a Single Pivot Cell

Fri, 10 Jan 2014 15:24:45 GMT

HI,

Thanks again for your help - works perfectly.

I'm now trying to include this solution in some other dashboards that I have. One which is proving difficult is where my pivot expression is already being calculated in a variable. My pivot expression is set to =$(vCalculatedNumber)

Variable for vCalculatedNumber :

IF(Value=1,

sum([Number]),

//%

IF(Value=2,

num(sum(Number)/sum([Line Total]),'#,##0.0%'),

//rate

IF(Value=3,

Round(sum(Number)/sum([Line Total]), '0.1'),

IF(Value=5,

num(sum(Number)/sum([Line Total]),'#,###.0'),

IF(Value=6,

num(sum([Number])/Pop_Count,'0.0000')

))))

there are also a few hundred indicators and I only need to concatenate about 6 to give three combined indicators. Can your solution be applied to this too??

Many thanks again for your time.

Tom

Re: Combining Dimension Values in a Single Pivot Cell

jerem1234 — Fri, 10 Jan 2014 15:49:30 GMT

You should be able to. Try this for dimension:

=if(match(Ind_No_X, 1, 2), 'Income', if(match(Ind_No_X, 8, 9), 'Discounts',Indicator_X))

This matches the 2 indicators you need to combine into one dimension. Should continue with if statements for whichever indicators you need to combine and have the field that lists the name of indicator last for everything else.

As for expression, if you don't need to do any totals, then this expression should work:

=if(not match(Ind_No_X,1,2,8,9), $(=vCalculatedNumber), concat($(=vCalculatedNumber), ' (',-right(Indicator_X, 1)) & ')')

where 1,2,8,9 are all the fields you are trying to combine.

Try playing around with the formula and see if it gets you anywhere if it doesn't work.

Hope this helps!

Re: Re: Combining Dimension Values in a Single Pivot Cell

Mon, 13 Jan 2014 15:14:43 GMT

Hi,

Thanks again for your reply. Ive had a go at your suggestion but with no luck. Ive attached a more detailed example of my document with the required variables and more datatypes. Really appreciate it if could show me where I am going wrong.

I've tried your suggestion for months 5 & 6 in the second pivot table.

Regards,

Tom

Re: Re: Re: Combining Dimension Values in a Single Pivot Cell

jerem1234 — Mon, 13 Jan 2014 19:03:16 GMT

Please find attached. I changed some things up like adding month as second dimension, as well as eliminating all the 'Month' expressions you had to just have one. Then had to use aggr to get the correct values. Also I had to compensate for getting total on the right.

The formula I used was:

=if(count(aggr($(vCalculatedNumber), [Indicator Number], Rolling_Month)) >1 and SecondaryDimensionality()<>0,

concat(aggr($(vCalculatedNumber), [Indicator Number], Rolling_Month), ' (', [Indicator Number]) & ')',

$(vCalculatedNumber))

Please find attached.

Hope this helps!

Re: Combining Dimension Values in a Single Pivot Cell

Tue, 14 Jan 2014 09:23:43 GMT

Hi Many thanks for your efforts and help in this. This is definately what I am after. The one thing I can see is that some indicators have been concatenated other than the specified ones. Any idea why? (eg indicator 531)

One last thing - i need to keep showing the previous rolling 12 months. Can this be included in the expression ? (Could a rolling_12_month flag be incorporated?)

Once again - thanks for your expertise. Greatly appreciated.

Tom

Re: Re: Combining Dimension Values in a Single Pivot Cell

jerem1234 — Tue, 14 Jan 2014 14:12:22 GMT

Oops I removed something from the if statement that should have stayed. It should be:

=if(count(aggr($(vCalculatedNumber), [Indicator Number], Rolling_Month)) >1 and SecondaryDimensionality()<>0 and match(min([Indicator Number]), 822, 823, 824, 825),

concat(aggr($(vCalculatedNumber), [Indicator Number], Rolling_Month), ' (', [Indicator Number]) & ')',

$(vCalculatedNumber))

As for the Rolling 12 months, you could use this in your dimension:

=if(date#(month & ' ' & year, 'MMM YYYY') > vMax, date#(month & ' ' & year, 'MMM YYYY'))

where vMax is:

=monthname(AddYears(max(date#(month & ' ' & year, 'MMM YYYY')), -1))

If you are going to do this, I'd create a MonthYear field in your script and then you get to make the 2 formulas above a little cleaner.

PFA

Hope this helps!

Re: Combining Dimension Values in a Single Pivot Cell

Tue, 14 Jan 2014 16:46:00 GMT

Perfect!!!!!!! Really really grateful for your help on this.

One really last little things..... if I wanted to show the last rolling 13 months , for example in this case Dec-2012 to Dec 2013 (basically I need to show 13 months worth of data upto the previous month) how could I amend the expression?

Many thanks again!!!

Re: Combining Dimension Values in a Single Pivot Cell

jerem1234 — Tue, 14 Jan 2014 17:23:09 GMT

To do by 13 months instead of twelve, you'll have to use the addmonths function instead of the addyears. The new expression for the variable vMax would be:

=monthname(AddMonths(max(date#(month & ' ' & year, 'MMM YYYY')), -13))

Re: Combining Dimension Values in a Single Pivot Cell

Wed, 15 Jan 2014 09:25:06 GMT

Hi,

Perfect!

Thank you so much for your time!! Most appreciated.

Tom