Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gal_polak
New Contributor III

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
Esteemed Contributor

Re: Sort by expression dimension with expression

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
rajeshthakur292
Contributor II

Re: Sort by expression dimension with expression

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

gal_polak
New Contributor III

Re: Sort by expression dimension with expression

yes,

didn't work

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

also didn't work.

Partner
Partner

Re: Sort by expression dimension with expression

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
Esteemed Contributor

Re: Sort by expression dimension with expression

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

gal_polak
New Contributor III

Re: Sort by expression dimension with expression

amazing! this work!

thank you very much!

YoussefBelloum
Esteemed Contributor

Re: Sort by expression dimension with expression

you're welcome

Good luck