Using Excel to Create Monthly Goals for Expression Background Colors In Graphs

Tue, 21 Feb 2012 23:35:36 GMT

Long title, I know.. What I'm trying to accomplish is this:

I have monthly goals to compare a KPI (Counts) against a goal on a monthly basis using conditional formatting. I represent the metric using a Bar Graph with the month as the X-axis and the variable (metric) as the Y-axis. I would like to not only integrate the Excel table with the monthly goals into the chart, but also write the Background Color expression for the bars in the graph to be GREEN if <= to the goal in the Excel table, or RED if > than the goal in the Excel table.

I am newer to the development of QlikView dashboards, but techincally sound. Please advise for the following questions:

1) How do I format the Excel table to compare against the months from the database the KPI is being derived from?

2) How do I integrate the Excel table into the chart?

3) How do I write the expression to compare the goals in the Excel table against the actuals of the variable (KPI Metric)?

Perhaps you can assist me with any of the three above. Anything will help get me closer, I'm sure.

Thanks you!

Using Excel to Create Monthly Goals for Expression Background Colors In Graphs

Wed, 22 Feb 2012 00:02:44 GMT

1) I believe you should have an excel like this

Month | Goal

Yo can have any mor info that you need i.e. Year, Customer, etc.

2) The integration will be made from the date fields and any other corresponding dimension

i.e if you have customer, your sales will have a customer and a date, also your goals will have a customer and date.

Here you will probable get some syntethic keys, but you can live with them.

3) In the background color just make the comparation:

if (sum(Amount) <= sum(goal), green(), red())

topic Using Excel to Create Monthly Goals for Expression Background Colors In Graphs in QlikView

Using Excel to Create Monthly Goals for Expression Background Colors In Graphs

Using Excel to Create Monthly Goals for Expression Background Colors In Graphs