Using a Month-level date field on a Continuous Axis

[millerhm]

Hey all - 

We have a very common date field we use called Service_Month that is used to refer to a whole month, but is saved as a date, i.e. 2020-07-01 (always the first of the month). We are easily able to convert this to the appropriate date format (usually something like 'MMM YY') as needed for display. 

However, when using this as an axis in a bar chart, we often have the chart overflow the space and start to scroll as there are more months than space, which is not what our users want to see. However, if we set the axis to Continuous, we get a really ugly skinny bar display that represents just the first of the month and leaves the rest of the month 'unrepresented' as such. This is the case whether we use date() functions or AutoCalendar.YearMonth etc. 

Is there some sort of floor() function we can use on a date to force it to be month-year only or some other way to make continuous? I feel like we are missing something obvious, but we're stuck - help?

[Lisa_P]

I would suggest turning off Continuous scale

[millerhm]

Yes, that is what we do since Continuous scale with this representation is not acceptable. What I'm looking for is a way to use Continuous scale that actually works. With Continuous off, our data often scrolls. Sometimes this is fine, but often times our end users would like to see the comprehensive view of the data that Continuous is meant for and do not want the data to scroll (or honestly even realize that it is). Also, Continuous I believe makes it more obvious when there is missing data, which is not as clear in non-Continuous mode. 

[millerhm · Accepted solution]

We have found a way around this by recasting the dual for this field as a consecutive number (rowno()) instead of the numeric value of the date (which is then ~30 integers between each month value). This seems to be sufficient to tell Qlik that there should not be any gaps between the values and thus it can display as expected in Continuous mode. 

Always happy to hear any other suggestions if you have them!

[darrinturner]

Love that you shared this with us. 

Could you post your expression before and after the change? Also a picture of the new chart would be awesome!

Again, thank you for sharing!

[PMR1015]

Hello @millerhm ,

Glad that you made it working. Can you please post the example of the approach you made it working. 

It would be helpful for other's, if there is bit more information on the steps. Thank you..