Friday, November 14, 2008

Putting a Count in the Chart Title

In my company we write a lot of QlikView reports to validate data. As part of our software projects we must check data to see that it matches assumptions and expectations. We also check the data against any other source available to validate data accuracy. Often, one of the simpler but important things on those reports is a count of things.

For example, a report might include a table box showing ITEM data. Instead of just showing the word ITEM in the table box title, we put a count of the ITEMs in the title. You can change the title on most sheet objects by right-clicking on the object and choosing Properties. The Title is in the General tab. The title might contain this:
=Num(Count(Distinct ITEM),'###,###') & ' ITEMs'

That gives us a count of the number of ITEMs that is formatted with the NUM function to use a comma as a thousands separator. The title then might show up on the chart as:
  1,220 Items

A List Box for the field named Location might contain this in the title:
=Num(Count(Distinct Location),'###,###') & ' Locations'

For another title you might want the number of sales orders in fiscal 2009. You can add an If test with a text expression like this:
=Num(Count(Distinct If(fiscal_yr='2008',order_id)),'###,###') & ' 2009 Orders'

These counts will change depending upon your current selection. The counts don't necessarily need to be in a title. The object title limits you to a single line of text. You can put the counts into a text box and that will allow you to show the information on multiple lines. If you type this into a text box:

=Num(Count(Distinct ITEM),'###,###') & ' ITEMs
' & Num(Count(Distinct Location),'###,###') & ' Locations
' & Num(Count(Distinct SDATE),'###,###') & ' Shipping Dates'

Note that the end-of-line or carriage return is part of the text. The text box will show up on the report saying something like this:
  1,220 Items
  15 Locations
  52 Shipping Dates

We have a report with a table box of SKU data where SKU ("stock-keeping unit") is a combination of item and location although the item and location fields are separate. The title for that table box contains this text expression that counts the number of occurrences of the concatenated item and location field values:
=Num(Count(Distinct ITEM&'@'&LOC),'###,###') & ' SKUs'

We often use charts with a cycle group in the dimension. That allows the report user to easily switch the chart dimensions. The current setting of the cycle group can be used in the report title if it makes sense. So, for a chart where the cycle group is named "Corp Group", you can make it a part of the chart title like this:
='Sales History by ' & GetCurrentField([Corp Group])

and then the chart title will switch between
  Sales History by Customer
  Sales History by Fiscal Month
  Sales History by Region

as the report user clicks to switch dimensions using the cycle group.

For a slightly more complex example of using the cycle group, consider a chart where the dimension is a cycle group named "Corp Group" and we want to include a count of dimension rows in the title. You could make the chart title like this:
='Sales History - ' & Num(Count(Distinct $(cg_label_1)),'###,###,###') & ' ' & GetCurrentField([Corp Group]) & 's'

You must also define a document variable named cg_label_1 that contains the name of the specific cycle group being used in the dimension. The variable would contain this text:

now the chart title will automatically switch between text like this as the cycle group changes:
  Sales History - 4,800 Customers
  Sales History - 12 Fiscal Months
  Sales History - 6 Regions


SamCham said...

Using your first example ina straight table:

=Num(Count(Distinct ITEM),'###,###') & ' ITEMs'

I get the total number of records in the data, but what I want is the total number of lines displayed in the straight table. Do you know any way to display this correctly?

-TB said...

I've seen that question posted in the QlikCommunity forum but I don't know if anyone has ever answered it. I don't know of any way to get the number or rows shown in a sheet object. It isn't necessarily the same as the number of dimension values since rows where all of the expressions are null are not shown.

A macro can get to the cell values from a chart. Possibly a macro could loop through the rows and count them.

Anonymous said...

HELP PLEASE! I am trying to count the dimension rows for a cyclic group. I did exactly as you posted. I defined the variable under 'Variable Overview', do I need to define it anywhere else

Anonymous said...

HELP PLEASE! I am trying to count the dimension rows for a cyclic group. I did exactly as you posted. I defined the variable under 'Variable Overview', do I need to define it anywhere else?

Anonymous said...

Its me again...I am also having issues counting when some rows in the second dimension are collapsed or expanded.