Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gal_polak
Creator
Creator

Sort by expression dimension with expression

Hi,

I know that we should try to include all logic in the script side and not in the UI,

but unfortunately in this case i needed to create a dimension with expression.

and this is my dimension:

if(

aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)<5,'Low (<5)',


if(

aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)<15

        AND

        aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)>5,'Medium',

       

            if(aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)>15,'High (>15)'

            )

        )


)



as you can see from this dimension i get 3 values:

1. Low (<5)

2. Medium

3. High (>15)


all i want to do is present it in bar chart in the following order:

High, Medium, Low


but how do i do it with "Sort by expression?"


1 Solution

Accepted Solutions
YoussefBelloum
Champion
Champion

EDITED


made a little syntax mistake

Hi,

try this:

if(

aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)<5,DUAL('Low (<5)',1),


if(

aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)<15

        AND

        aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)>5,'DUAL(Medium',2),

      

            if(aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)>15,DUAL('High (>15)',3)

            )

        )


)



==> on the sorting properties, sort by numeric value

View solution in original post

6 Replies
Neymar_Jr
Creator II
Creator II

Did you try match(YourExp,'Low (<5)','Medium','High (>15)')  ?

gal_polak
Creator
Creator
Author

yes,

didn't work

even tried to change the text to numbers, and use the numbers like 1,2,3,

also didn't work.

tulabandula
Partner - Contributor III
Partner - Contributor III

Try below once.,

Copy the same expression as dimension logic and paste it in Sort Tab -->Sort By -Expression as Asce or Desc by making sure applying on the right dimension..

Regards,

Sreeni.

YoussefBelloum
Champion
Champion

EDITED


made a little syntax mistake

Hi,

try this:

if(

aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)<5,DUAL('Low (<5)',1),


if(

aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)<15

        AND

        aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)>5,'DUAL(Medium',2),

      

            if(aggr(Sum({$<$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Hidden Users*'),'DisplayInLeaderboard=','DisplayInLeaderboard={1}')),

$(=if(WildMatch(GetFieldSelections(_UserFilters), '*Show Inactive Users*'),'IsActive=','IsActive={1}')), WasLoginPotential={1},[Property Name]=>}WasLoggedIn),UserID)>15,DUAL('High (>15)',3)

            )

        )


)



==> on the sorting properties, sort by numeric value

gal_polak
Creator
Creator
Author

amazing! this work!

thank you very much!

YoussefBelloum
Champion
Champion

you're welcome

Good luck