Qlik Community

QlikView Documents

Documents for QlikView related information.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.

Formatted Report Type Chart using Valuelist

john_s_w_wood
Contributor

Formatted Report Type Chart using Valuelist

I am often presented with a challenge to deliver a formatted report type output on a dashboard. The type of output which would normally be produced in Crystal Reports, SQL Server Reporting Services or even XL.

There's a nice example on the Qlik demonstrations - Data Visualizations - Butterfly Chart, on how to do it but it can be a bit of a chore to set up.

There is another way using the often overlooked valuelist (or valueloop) function. Please see the attached example.

formatreport.JPG

See the following in the Chart properties:

Valuelist function as a Dimension Expression

Use of rowno and pick functions in the Expression

use of rowno in Text Format option of Expression

Please not with a lot of data and a lot of rows in the format, refresh can be delayed, If its an issue, consider using an aggregated data load to populate.

Attachments
Comments
msteedle
Contributor

We have found that this type of solution calculates significantly faster with large data using an island table rather than ValueList for the list of metric names. You would also then use an index column in the island table rather than RowNo for the first parameter of the Pick function. This also gives you the option of adding more columns to the island table to store formatting strings (ex. '<b>'), so no conditional logic is needed in the chart, other than the chart expression, itself.

john_s_w_wood
Contributor

Perfectly valid point Michael. Its one of those quick wins and easier to show non technical developers such as people with Excel only skills such as Finance, Sales & Marketing types.

I have especially used it in Qlikview Report writer where refresh time is not critical. Thanks for the feedback by the way.

hirishv7
Honored Contributor

Hi,

Thanks!!!  Simple way by not using match again.

manojkulkarni
Valued Contributor II

Thank's for sharing. It's good example while preparing PnL reports

Version history
Revision #:
1 of 1
Last update:
‎05-14-2015 05:16 AM
Updated by: