Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
Jennell_McIntire
Employee
Employee

The Demo Team recently built a mashup for the 2016 Oscars where we analyzed the nominees and award winners over the past 88 years.  The design for the mashup often showed nominees and winners in the same chart as seen below where the silver bar indicated nominations and the gold bar represented wins.

Example of bar charts.png

One requirement for the app was to color the number of nominations bar silver and the number of wins bar gold.  In the data model, we did not have a field that we could use as the dimension to show the coloring for nominations and winner so at the suggestion of Alexander Karlsson we used the ValueList chart function.  As noted in Qlik Sense Help, “ValueList() returns a set of listed values, which, when used in a calculated dimension, will form a synthetic dimension.”  In the Actresses bar chart above, we created the dimension using this syntax:

 

ValueList('Oscars', 'Nominations')

 

Thus creating a synthetic dimension with two possible values: Oscars and Nominations.  Then in the measure, we used an If statement to assign a measure to each list item in our ValueList dimension.

 

If(ValueList('Oscars', 'Nominations')='Oscars',

     Count({<Actress_Win_flg={'1'} >} Distinct Film),

     (Count(Distinct Film)-Count({<Actress_Win_flg={'1'} >} Distinct Film)))

 

If the dimension is ‘Oscars’ then we count the films where the actress won.  Otherwise, we count the nominations where the actress did not win.  This same logic was used to handle the coloring of the bars.  We colored the bars by using this expression:

 

If(ValueList('Oscars', 'Nominations')='Oscars', '#e1aa3d', '#ccc')

 

If the dimension is Oscars showing the wins, then color the bar gold otherwise color the bar silver.

 

The ValueList chart function provided what we needed to treat nominations and winners as if they were a dimension providing us the flexibility to vary the measures and coloring we wanted to use on the bars in the bar chart.  This solution saved us time from having to modify our data model to accommodate our design requirements.  The only difference between the ValueList synthetic dimension and a normal dimension is synthetic dimensions are not affected by selections.  Alexander Karlsson wrote a blog about ValueList as well as ValueLoop – check it out.

 

Thanks,

Jennell

9 Comments
Gysbert_Wassenaar

Yes, it's nice that there's a workaround for not being able to assign a color expression to a measure. But I do hope this workaround will be made redundant very soon.

2,353 Views
oric1884
Contributor III
Contributor III

Jennell, nice post. Thank you for share.

Is it possible to reproduce it in Qlik sense? If yes, could you please give us more details?

Best regards,

0 Likes
2,353 Views
Jennell_McIntire
Employee
Employee

Hi Ciro,

The back-end for the Oscars mash-up is a Qlik Sense app so the code for the dimension, measure and coloring that I refer to were all done in the Qlik Sense app.

Jennell

0 Likes
2,353 Views
oric1884
Contributor III
Contributor III

Jennell, thank you for reply.

Your post opened for me, as advanced Excel developer, fantastics opportunities with Qlik Sense.

But, only with your snepped formulas, I have no idea about how to reach this amazing chart.

Could you please help me with a short exemple?

Many thank for any kind of help,

Best regards,

Ciro.

0 Likes
2,353 Views
msteedle
Luminary Alumni
Luminary Alumni

In our testing, we found that synthetic dimensions using island tables calculated considerably faster than ValueList in large datasets: Synthetic Dimensions with Better Performance than Valuelists « BI Commons

HT skokenes

0 Likes
2,353 Views
NareshGuntur
Partner - Specialist
Partner - Specialist

Thanks Jennel for the post.

I used it for the similar scenario and it works. However, have you tried using variables instead of the count calculation in the expression.

like having Count({<Actress_Win_flg={'1'} >} Distinct Film)

this in the variable and using this variable


This doesn't work for me.


Cheers,

Naresh

0 Likes
2,353 Views
Jennell_McIntire
Employee
Employee

Naresh,

Yes, a variable can replace the measure.  In place of the measure, simply enter $(variablename).

Jennell

0 Likes
1,905 Views
prasoon1852
Contributor II
Contributor II

Hey jmc ,

How can i restrict my dimension to show only top 10 values in this case ? I tried all possible ways but nothing is working.

Prasoon

0 Likes
1,905 Views
Jennell_McIntire
Employee
Employee

Hi Prasoon,

You can set a limit on the dimension (like the Actor or Actress in my example).  In the properties for the dimension, under Limitation, you can select Fixed number and set it to Top 10.

Hope this helps.

Jennell

0 Likes
1,905 Views