Year Month Day

Today I would like to share a little bit of technical QlikView insight with you. Business asked me to format the number of years a certain resident has been around as a client in a chart using not a decimal, but an interval: Y – M – D.

graph

Here the test-data I worked with - so you can check that the calculation is done right:
test-data

I started by adding some temporary fields in the data model calculating the number of year, months and days, starting from the number of years in its decimal format. This way I could use the temp-fields in a concatenation: years & ‘y ‘ & months & ‘m ‘ & days to construct the LabelStay field.
script
Note that I dropped the fields I didn’t need in the front to keep the model as clean as possible. This is not shown in the picture.

After that I changed the existing bar chart to a combo chart.
general
I used the resident as the dimension and created two expressions: sum ([Stay in years]) and LabelStay.
expressions_stay
The former I used to display the bars, the latter to show the labels.
expressions_label