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: 
Anonymous
Not applicable

Issue about date dimensions

Hi all,

For a bar chart, I would like to visualize data by week year, two or more years at the same time but without aggregating on the same column different years' results..

What I'm having now is this (For 2017 and 2018)

2.png

I would like to have was something like  this:

1.png

Does anyone knows if is it possible?

Thank you!

Sofia Vaz

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

Sure....

The dimension looks like this:

=Dual(    If(FMod(Week,13)=1,Year&'-')&Week&Repeat(' ',$(=Max(Year))-Year)   ,    Year*100+Week)

The Dual() function is used to have a display value that is treated as a text and a numeric value that is use for calculations. The text part which is the first parameter is before the very last comma in the expression. I have added a lot of spaces for clarity in the expression above.

By using the FMod(Week,13=1 I can add some text every 13th Week. The added text is the Year and it gets prepended along with a dash/hyphen before the Week number. Then comes the week number. Lastly "invisible" spaces are added as replacements for the years so Qlik have for instance week 4 in 2017 different from week 4 in 2018 even though it doesn't look like it since the spaces are "invisble".

If the trick is hard to understand you could simply use this expression:


=Dual(Year&'-'&Week, Year*100+Week)


Then every Week will have a year in front of it - the bar chart gets less readable but it works the same.


View solution in original post

9 Replies
sunny_talwar

In QlikView you can add Year as your second dimension, but not sure if Qlik Sense allow it or not... have you tried adding year as the second dimension?

petter
Partner - Champion III
Partner - Champion III

It is not possible in a bar chart to have two dimensions and multiple measures in Qlik Sense.

The way to achieve it is to combine the year and week into one dimension and make some logic to not include the year into label of the dimension more than every 13th week or so - each quarter approximately. Then you can get a bar chart like this:

2018-04-03 14_06_38-Temp _ Sheets - Qlik Sense.png

Anonymous
Not applicable
Author

yes i've tried. If I add them as a second dimension I'm forced to choose one of them, but i'm not allowed to see weeks of 2017 and weeks of 2018 separately 

Anonymous
Not applicable
Author

Thank you Petter! I can only achieve this by creating a new field on the script, or it can be done on the app?

petter
Partner - Champion III
Partner - Champion III

It's your choice.... actually I did make a new field in the script of the attached application. But in the bar chart I made a calculated dimension - not using the field created in the script.... So you can have a look at both approaches.

Anonymous
Not applicable
Author

Thank you Peter! I'm not able to open your file... can you please share here the expression of that calculated dimension, please?

petter
Partner - Champion III
Partner - Champion III

Sure....

The dimension looks like this:

=Dual(    If(FMod(Week,13)=1,Year&'-')&Week&Repeat(' ',$(=Max(Year))-Year)   ,    Year*100+Week)

The Dual() function is used to have a display value that is treated as a text and a numeric value that is use for calculations. The text part which is the first parameter is before the very last comma in the expression. I have added a lot of spaces for clarity in the expression above.

By using the FMod(Week,13=1 I can add some text every 13th Week. The added text is the Year and it gets prepended along with a dash/hyphen before the Week number. Then comes the week number. Lastly "invisible" spaces are added as replacements for the years so Qlik have for instance week 4 in 2017 different from week 4 in 2018 even though it doesn't look like it since the spaces are "invisble".

If the trick is hard to understand you could simply use this expression:


=Dual(Year&'-'&Week, Year*100+Week)


Then every Week will have a year in front of it - the bar chart gets less readable but it works the same.


Anonymous
Not applicable
Author

Thank you very much, Petter! That works perfectly!

petter
Partner - Champion III
Partner - Champion III

You're welcome happy to help.