Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Value in Pivot table

Hi all,

I am creating a pivot table, where i am using custom dimensions like below:

NewDims:

LOAD * INLINE [

      25 New Members,10 New Members

    Invitation Email, Invitation Email

    Reminder1 (signups), Reminder1 (signups)

    Reminder1 (non signups),Reminder1 (non signups)

    Reminder1 ,Reminder1

    Reminder2 (signups),Reminder2 (signups)

    Reminder2 (non signups),Reminder2 (non signups)

    Reminder2, Reminder2

    Final Reminder (signups), Final Reminder (signups)

    Final Reminder (non signups), Final Reminder (non signups)

    Final Reminder,Final Reminder

];

The above dimensions comes in 1st column, in 2nd column i need to put the count.

IF(match([25 New Members] , 'Invitation Email'),sent))

Now, I need to filter the sent count for particular type. how can i use the set analysis without using the aggregation.

For example: sum({$<email_type_id = {44}, campaign_name_id = {"$(=max(campaign_name_id)-11)"}>}sent)

Will give the sum of sent count for particular email_type_id  and campaign_name_id,

I have only one row for email_type_id =44 and each campaign_name_id so i don't want to sum just i need to get the value of sent in that row.

please help on this.

Thanks,

Pramod

1 Solution

Accepted Solutions
Not applicable
Author

I found the answer

IF(match([$25 New Members] , 'Invitation Email'),Only({$<campaign_name_id={"19"},email_type_id = {'44'}>}sent))

View solution in original post

13 Replies
Anonymous
Not applicable
Author

your problem??

Not applicable
Author

Sorry, i have edited my question please check and help.

Anonymous
Not applicable
Author

is this?

=sum({$<email_type_id = {'44'}>}sent)

Not applicable
Author

No, I don't want the sum just i want to get the "sent" value from the row where email_type_id = {'44'} and campaign_name_id  ={'143'}

Anonymous
Not applicable
Author

like this:

if(email_type_id = '44' and campaign_name_id  ='143', sent)

Not applicable
Author

Yes but above is not working

Anonymous
Not applicable
Author

may be this:


only({<email_type_id = {'44'} and campaign_name_id  ={'143'}>} sent)

Not applicable
Author

I am trying to create a pivot table like below.

2.png

I have used inline in script to get the dimension [25 New Members] in table.

i have attached the sample data.

For example

In sample data campaign_name_id = 19 in campaign table.

I need the sent count of email_type_id = 44 in pivot table. (email_type_id ,44 = Invitation Email)

So for that i have return measure like below but not working.

IF(match([$25 New Members] , 'Invitation Email'),if(email_type_id = '44' and campaign_name_id= '19', sent),sent,

IF(match([$25 New Members] , 'Reminder1 (signups)'),if(email_type_id = '2' and campaign_name_id= '19', sent),sent,

IF(match([$25 New Members], 'Reminder1 (non signups)'),if(email_type_id = '45' and campaign_name_id= '19', sent),sent,

IF(match([$25 New Members], 'Reminder2 (signups)'),if(email_type_id = '3' and campaign_name_id= '19', sent),sent,

IF(match([$25 New Members], 'Reminder2 (non signups)'), if(email_type_id = '46' and campaign_name_id= '19', sent),sent,

)))))

help to get the corresponding sent count.

Anonymous
Not applicable
Author

Try this:

IF(match([$25 New Members] , 'Invitation Email') and email_type_id = '44' and campaign_name_id= '19', sent,

IF(match([$25 New Members] , 'Reminder1 (signups)') and email_type_id = '2' and campaign_name_id= '19', sent,

IF(match([$25 New Members], 'Reminder1 (non signups)') and email_type_id = '45' and campaign_name_id= '19', sent

IF(match([$25 New Members], 'Reminder2 (signups)') and email_type_id = '3' and campaign_name_id= '19', sent,

IF(match([$25 New Members], 'Reminder2 (non signups)') and email_type_id = '46' and campaign_name_id= '19', sent)))))

//Check no of brackets closed in the end