topic Re: Trouble creating pivot chart in QlikView

Trouble creating pivot chart

Mon, 02 Jun 2014 19:09:24 GMT

I have data coming in that needs to be distinguished by the cust id and some other criteria.

I basically want my qlikview document to look like this:

		Q114			Q413
	CN	EN	Total	CN	EN	Total

Sally James	20,167	3,290	23,457	17,094	2,129	19,223
Sally James thru POS	385	372	757	842	1,009	1,850
Jimmy Brown	8,315	1,149	9,464	4,757	2,233	6,990
Jimmy Brown thru POS	109	81	190	220	2	222
Danielle Jenkins	1,579	135	1,714	3,835	224	4,059
Danielle Jenkins thru POS	66	115	181	21	140	160

To get Sally James I have the following as a dimension:

=if((Match([Res ID],'100529','400068','104198','609022') or [Res Direct Grandparent ID]='CTL01') and [Disti LOB]=39,'Sally James')

Sally James thru POS

=if((Match([Res ID],'100529','400068','104198','609022') or [Res Direct Grandparent ID]='CTL01') and [Disti LOB]<>39,'Sally James thru POS')

Jimmy Brown

=if((Match([Res ID],'100522','603207','608094') or [Res Direct Grandparent ID]='VRZ01') and [Disti LOB]=39,'Jimmy Brown')

Jimmy Brown thru POS

=if((Match([Res ID],'100522','603207','608094') or [Res Direct Grandparent ID]='VRZ01') and [Disti LOB]<>39,'Jimmy Brown thru POS')

Danielle Jenkins

=if((Match([Res ID],'102786','104805','608095', '609482') or [Res Direct Grandparent ID]='ATT01') and [Disti LOB]=39,'Danielle Jenkins')

Danielle Jenkins thru POS

=if((Match([Res ID],'102786','104805','608095', '609482') or [Res Direct Grandparent ID]='ATT01') and [Disti LOB]<>39,'Danielle Jenkins thru POS')

I tried setting up a group with each of these as dimensions but I can't get them all to show up.

My pivot table has dimensions

calculated dimension,

Year_Quarter

Division

expression is the following:

=round(sum({<[CTA Duplicate]={'No'},[Res Location]={'DOM'}>}$(vExtEffCommPrice))/1000)

not sure how I can get each of these dimensions to display like the example

any suggestions????????

Message was edited by: Michelle Malone I have added the script file.

Re: Trouble creating pivot chart

Mon, 02 Jun 2014 20:23:19 GMT

Try to prepare your dimension with names in load script!

Re: Trouble creating pivot chart

Mon, 02 Jun 2014 21:24:46 GMT

I put the following in my load statement but I don't seem to have any data appearing in Domestic Tier 1.

Did I do the if conditions wrong???

if ((Match([Res ID],'100529','400068','104198','609022') or [Res Direct Grandparent ID]='CTL01') and [Disti LOB]=39,'Centurylink'), if((Match([Res ID],'100529','400068','104198','609022') or [Res Direct Grandparent ID]='CTL01') and [Disti LOB]<>39,'Centurylink thru POS'),

if((Match([Res ID],'100522','603207','608094') or [Res Direct Grandparent ID]='VRZ01') and [Disti LOB]=39,'Verizon'), if((Match([Res ID],'100522','603207','608094') or [Res Direct Grandparent ID]='VRZ01') and [Disti LOB]<>39,'Verizon thru POS'),

if((Match([Res ID],'102786','104805','608095', '609482') or [Res Direct Grandparent ID]='ATT01') and [Disti LOB]=39,'AT&T'), if((Match([Res ID],'102786','104805','608095', '609482') or [Res Direct Grandparent ID]='ATT01') and [Disti LOB]<>39,'AT&T thru POS') as [Domestic Tier 1]

Re: Trouble creating pivot chart

Mon, 02 Jun 2014 21:27:33 GMT

May you attach your script?

Re: Trouble creating pivot chart

Mon, 02 Jun 2014 21:39:23 GMT

See initial post; I attached the script file there.

Re: Trouble creating pivot chart

Mon, 02 Jun 2014 22:05:03 GMT

First of all, i'am sure, that there is something not right with brackets in your nested if.

Your condition should look like this:

if(cond1,if(cond2,result12,result13),result4) as some_column

this will give you as result result12 if condition 1 and condition2 is met, result13 if condition1 is met but condition2 is not true, result4 - if conditio1 is not true.

Try to simplify your if for the beggining. Do only first, then, if first will be ok, add next, etc.

When you put your coursor near some bracket, editor will show you it, and its pair in red. This helps to check if pairs are where you expect it.

regards

Darek