Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
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.

Highlighted
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