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.
Here the test-data I worked with - so you can check that the calculation is done right:
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.
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.
I used the resident as the dimension and created two expressions: sum ([Stay in years]) and LabelStay.
The former I used to display the bars, the latter to show the labels.