topic Re: Cross Tab in QlikView

Cross Tab

vijetas42 — Thu, 21 Aug 2014 10:36:42 GMT

Hi,

I need help on one scenario where i have to use cross table for that i need cross tab shown in below image so for how should be my data in excel

Regards,

Vijeta

Re: Cross Tab

senarath — Thu, 21 Aug 2014 10:42:12 GMT

Hi,

I suppose you are talking about Email, Direct Mail and Speaker Programs. Basically there is no feature as such but still you can handle it.

Filed here you get these should be added as a Dimension. After it drag that column to the header level

you can create a synthetic dimension using Valuelist function.

Synthetic Dimensions - ValueList() (Example)

Attached is a one I had taken previously from community.

thanx

Senarath

Re: Cross Tab

vijetas42 — Thu, 21 Aug 2014 10:50:52 GMT

Thanks for reply but,Can you explain more about how to add adhoc (inline) dimension at qlikview end

Re: Cross Tab

senarath — Thu, 21 Aug 2014 10:54:33 GMT

Add a calculated dimension as below

=Valuelist('Email', 'Direct Mail','Speaker Programs')

So you can give conditions in the expressions as;

=if(Valuelist('Email', 'Direct Mail','Speaker Programs')='Email',sum(field)) and so on

Please refer attached link for further details.

Re: Re: Cross Tab

ashfaq_haseeb — Thu, 21 Aug 2014 11:11:13 GMT

Have a look at attached application.

Regards

ASHFAQ

Re: Cross Tab

vijetas42 — Thu, 21 Aug 2014 11:47:22 GMT

Hi,

Thanks for the reply

but, I have 3 values in my list and while following same way as you did it is repeating for all the column measures

just find the below expression and suggest where I am missing out,

=if(ValueList('Email','Direct Mail','Speaker Program')='Email',Max([Email Open Rate]),Sum([Direct Mail Sent]),Sum[Speaker Program no of Attendees])

Regards,

Vijeta

Re: Cross Tab

senarath — Thu, 21 Aug 2014 11:58:48 GMT

Hi,

There should be 03 separate expressions for 03 columns that you want;

01.

=if(ValueList('Email','Direct Mail','Speaker Program')='Email',Max([Email Open Rate])

02.

=if(ValueList('Email','Direct Mail','Speaker Program')='Direct Mail',Sum([Direct Mail Sent])

03.

=if(ValueList('Email','Direct Mail','Speaker Program')='Speaker Program',Sum[Speaker Program no of Attendees])

thanks