Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Value in Pivot table

I found the answer

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

13 Replies
balrajahlawat
Esteemed Contributor

Re: Value in Pivot table

your problem??

Not applicable

Re: Value in Pivot table

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

balrajahlawat
Esteemed Contributor

Re: Value in Pivot table

is this?

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

Not applicable

Re: Value in Pivot table

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'}

balrajahlawat
Esteemed Contributor

Re: Value in Pivot table

like this:

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

Not applicable

Re: Value in Pivot table

Yes but above is not working

balrajahlawat
Esteemed Contributor

Re: Value in Pivot table

may be this:


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

Not applicable

Re: Value in Pivot table

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.

balrajahlawat
Esteemed Contributor

Re: Value in Pivot table

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