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

How do you create a group out of a calculation?

Hello!

This is my first post and my first time using Qlik Sense so hopefully I've come to the right place!

I'm working with social media data, and within the data set, I have people who've mentioned my brand from 1 - 500 times. I'd like to group these as different sets:

Group A: mentioned brand 100 - 500 times

Group B: mentioned brand 25 - 99 times

Group C: mentioned brand 12 - 24 times

Group 😧 mentioned brand 6 - 11 times

The data simply lists all the mentions so the 'people' column in the data set will have duplicate records.

Not only do I want to group these people, I'd like to know how many people are in each group as a KPI and how many total mentions each group has.

Any help would be appreciated.

1 Solution

Accepted Solutions
Not applicable
Author

try the following approach (you must edit FROM stetement with your source definition):



MyTable:
LOAD
"Query Id",
"Query Name",
"Date",
Title,
Snippet,
Url,
Domain,
Sentiment,
"Page Type",
Language,
// "Country Code",
//     "Continent Code",
    Continent,
Country,
// "State Code",
//     "County Code",
//     "City Code",
    Notes,
"Account Type",
Assignment,
"Author"
FROM [...];



Left Join (MyTable)
CalculationTable:
LOAD
Author,
if(TotalMentions <= 11, 'D',
if(TotalMentions > 11 and TotalMentions <= 24, 'C',
if(TotalMentions > 24 and TotalMentions <= 99, 'B',
if(TotalMentions > 99, 'A'
))))
as AuthorClass;
LOAD
Author,
Count(Author) as TotalMentions
Resident MyTable
Group By Author;


View solution in original post

6 Replies
Not applicable
Author

Can you post your script or an example qvw?

Not applicable
Author

Here's the load script:

LOAD

    "Query Id",

    "Query Name",

    "Date",

    Title,

    Snippet,

    Url,

    Domain,

    Sentiment,

    "Page Type",

    Language,

//     "Country Code",

//     "Continent Code",

    Continent,

    Country,

//     "State Code",

//     "County Code",

//     "City Code",

    Notes,

    "Account Type",

    Assignment,

    "Author",

This is the expression I used in a bar chart to show me the mentions by author for a group:

if( Count(Author)>99,

null(),

if( Count(Author)<25, null(),

Count(Author)))

I want to make this group a dimension.

Not applicable
Author

try the following approach (you must edit FROM stetement with your source definition):



MyTable:
LOAD
"Query Id",
"Query Name",
"Date",
Title,
Snippet,
Url,
Domain,
Sentiment,
"Page Type",
Language,
// "Country Code",
//     "Continent Code",
    Continent,
Country,
// "State Code",
//     "County Code",
//     "City Code",
    Notes,
"Account Type",
Assignment,
"Author"
FROM [...];



Left Join (MyTable)
CalculationTable:
LOAD
Author,
if(TotalMentions <= 11, 'D',
if(TotalMentions > 11 and TotalMentions <= 24, 'C',
if(TotalMentions > 24 and TotalMentions <= 99, 'B',
if(TotalMentions > 99, 'A'
))))
as AuthorClass;
LOAD
Author,
Count(Author) as TotalMentions
Resident MyTable
Group By Author;


Not applicable
Author

You can use dimension AuthorClass in front end envirorment and apply, for example, the expression Count(Author) or Count(DISTINCT Author) to perform calculation.

Not applicable
Author

That's great! Worked a treat. Cheers Carlo!

Michael_Tarallo
Employee
Employee

HI Tara - there is also a video on this topic here Qlik Sense - Dimension Grouping (video) that shows this process defined in the master items vs the load script:

That video is part of a subset of other videos that can help you will Qlik Sense.

New to Qlik Sense Videos

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Regards,

Mike Tarallo

Qlik

Regards,
Mike Tarallo
Qlik