Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

sofiavaz
Contributor

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
MVP
MVP

Re: Issue about date dimensions

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.


9 Replies

Re: Issue about date dimensions

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?

MVP
MVP

Re: Issue about date dimensions

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

sofiavaz
Contributor

Re: Issue about date dimensions

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 

sofiavaz
Contributor

Re: Issue about date dimensions

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

MVP
MVP

Re: Issue about date dimensions

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.

sofiavaz
Contributor

Re: Issue about date dimensions

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

MVP
MVP

Re: Issue about date dimensions

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.


sofiavaz
Contributor

Re: Issue about date dimensions

Thank you very much, Petter! That works perfectly!

MVP
MVP

Re: Issue about date dimensions

You're welcome happy to help.

Community Browser