Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Groups in table

Hi all,

I am using below expression to get the count in straight table. I need to add the counts of metrics_email_type_id 2 and 45 and need to show the sum in the column below the count of 45.

As explained i need the count of 3 and 46 below 46, 4 and 47 below 47.

IF(match(metrics_email_type_id, '44'), sent,

IF(match(metrics_email_type_id, '2'), sent,

IF(match(metrics_email_type_id, '45'), sent,

IF(match(metrics_email_type_id, '3'), sent,

IF(match(metrics_email_type_id, '46'), sent,

IF(match(metrics_email_type_id, '4'), sent,

IF(match(metrics_email_type_id, '47'), sent,

IF(match(metrics_email_type_id, '126'), sent,metrics_email_type_id))))))))

How can i achieve this?

Regards,

Kumar

Tags (1)
6 Replies
balrajahlawat
Esteemed Contributor

Re: Groups in table

Is this you are asking?

sum(IF(match(metrics_email_type_id, '3','46',4','47'),count(metrics_email_type_id)) as Total Counts

Not applicable

Re: Groups in table

IF(match(metrics_email_type_id, '44'), sent,

IF(match(metrics_email_type_id, '2'), sent,

IF(match(metrics_email_type_id, '45'), sent,

//here the sum of 2 and 45

IF(match(metrics_email_type_id, '3'), sent,

IF(match(metrics_email_type_id, '46'), sent,

//here the sum of 3 and 46

IF(match(metrics_email_type_id, '4'), sent,

IF(match(metrics_email_type_id, '47'), sent,

//here the sum of 4 and 47

IF(match(metrics_email_type_id, '126'), sent,metrics_email_type_id))))))))

balrajahlawat
Esteemed Contributor

Re: Groups in table

like this?

IF(match(metrics_email_type_id, '44'), sent,

IF(match(metrics_email_type_id, '2'), sent,

IF(match(metrics_email_type_id, '45'), sent,

IF(match(metrics_email_type_id, '2','45'), sum(count(metrics_email_type_id)), //u can use distinct as well

IF(match(metrics_email_type_id, '3'), sent,

IF(match(metrics_email_type_id, '46'), sent,

IF(match(metrics_email_type_id, '3','46'), sum(count(metrics_email_type_id)), //u can use distinct as well

IF(match(metrics_email_type_id, '4'), sent,

IF(match(metrics_email_type_id, '47'), sent,

IF(match(metrics_email_type_id, '4','47'), sum(count(metrics_email_type_id)), //u can use distinct as well

IF(match(metrics_email_type_id, '126'), sent,metrics_email_type_id))))))))

Not applicable

Re: Groups in table

Using below expression as dimension in first column,

I need to add total in between how can i hardcode total in between

IF(match(metrics_email_type_id, '44'), Replace(metrics_email_type_id, '44', 'InvitationEmail'),

IF(match(metrics_email_type_id, '2'), Replace(metrics_email_type_id, '2', 'Signed First Reminder'),

IF(match(metrics_email_type_id, '45'), Replace(metrics_email_type_id, '45', 'Not Signed First Reminder')

//Total of first reminder

IF(match(metrics_email_type_id, '3'), Replace(metrics_email_type_id, '3', 'Signed Second Reminder'),

IF(match(metrics_email_type_id, '46'), Replace(metrics_email_type_id, '46', 'Not Signed Second Reminder'),

//Total of second reminder

IF(match(metrics_email_type_id, '4'), Replace(metrics_email_type_id, '4', 'Signed Third Reminder'),

IF(match(metrics_email_type_id, '47'), Replace(metrics_email_type_id, '47', 'Not Signed Third Reminder'),

//Total of third reminder

IF(match(metrics_email_type_id, '126'), Replace(metrics_email_type_id, '126', 'Not Signed Fourth Reminder'),

metrics_email_type_id))))))))

balrajahlawat
Esteemed Contributor

Re: Groups in table

I would say create Group like at script level:

IF(match(metrics_email_type_id, '44','2','45'), Group1

IF(match(metrics_email_type_id, '3','46'), Group2)) as Group

After this add Group as 1st Dimension, 2nd dimension would be your above expression

and then write following as Expression:  sum(count(metrics_email_type_id))

Not applicable

Re: Groups in table

report.png

I need a table like above not as group, in third row i need a sum of first two,

same way in sixth row i need a count of 4th and 5th column.