Mini chart for alerts, text objects and popups

I was handed and interesting challenge yesterday.  The challenger was looking for a way to present both a list of dimensions, and an associated calculation, in a popup dialog.  This sounds like it should be quite achievable, but I couldn't think of an answer immediately!

We can, of course, present the list of dimensions using Concat:

=Concat(Customer, chr(10))

We can add a sort

=Concat(Customer, chr(10), -Aggr(Sum(LineValue),Customer))

If we tried to add the Sum into the Concat's first parameter, we would get an error about not allowing an aggregation.

We were racking our brains to come up with a way to add the value (without seeing the bleeding obvious! - see below).  Eventually, I came up with this horrendous expression:

=If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),1))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),1)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),2))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),2)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),3))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),3)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),4))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),4)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),5))>0, 
FirstSortedValue(Customer, -Aggr(Sum(LineValue), Customer),5)
& ' - ' & Num(Sum({} LineValue), '#,##0')
, Null())

This just shows the top 5 - but only if there is no match on the expression - in which case it would hide the matching values (because firstsortedvalue returns null).  So I had some thoughts and came up with this:

=If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),1))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),1)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),2))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),2)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),3))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),3)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),4))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),4)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null()) 

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),5))>0, 
FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),5)
& ' - ' & Num(Sum({} LineValue), '#,##0')
& chr(10), Null())

& If(Len(FirstSortedValue(DISTINCT Customer, -Aggr(Sum(LineValue)+Rand(), Customer),6))>0, 
'Others'
& ' - ' & Num(Sum(LineValue)-Sum({} LineValue), '#,##0')
, Null())

This adds a small rand() value that appears to break the match enough to make things work - I am not sure if it will work on all occasions, but in several tests it did work.  So, it does solve a problem where I want to see just the top 5 and "Others".

But it doesn't show all the dimensions plus values - which was the original challenge.

A rethink was in order and I returned to the Concat with the sort.  I wonder...  If the sort expression accepts an Aggr, would that be allowed in the first parameter:

=Concat(Customer & ' - ' & Num(Aggr(Sum(LineValue), Customer), '#,##0'), chr(10), -Aggr(Sum(LineValue), Customer))

Yay!  It worked!!!

Potentially, this could be used in text objects, alert texts, and popup texts.

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

No nodistinct

This article is taken from my new book - Mastering QlikView.  Available from 26th November.

---

The Aggr function has, as an optional clause, the possibility of stating that the aggregation will be either distinct or nodistinct.

The default option is distinct and, as such, is rarely ever stated.  In this default operation, the aggregation will only product distinct results for every combination of dimensions - just as you would expect from a normal chart or straight table.

The nodistinct option only makes sense within a chart, one that has more dimensions than are in the Aggr statement.  In that case, the granularity of the chart is lower than the granularity of the Aggr and therefore QlikView will only calculate that Aggr for the first occurrence of lower granularity dimensions and will return null for the other rows.  If we specify nodistinct then the same result will be calculated across all of the lower granularity dimensions.

That can be difficult to understand without seeing an example, so let us look at a common use case for this option.  We will start with a data set:

ProductSales:
Load * Inline [
Product, Territory, Year, Sales
Product A, Territory A, 2013, 100
Product B, Territory A, 2013, 110
Product A, Territory B, 2013, 120
Product B, Territory B, 2013, 130
Product A, Territory A, 2014, 140
Product B, Territory A, 2014, 150
Product A, Territory B, 2014, 160
Product B, Territory B, 2014, 170
];

We will build a report from this data using a pivot table:

Now, we want to bring the value in the Total column into a new column under each year, perhaps to calculate a percentage for each year.  We might think that, because the total is the sum for each Product and Territory then we might use an Aggr like this:

   Sum(Aggr(Sum(Sales), Product, Territory))

However, as stated above, because the chart includes an additional dimension (Year) than the Aggr, then the expression will only be calculated for the first occurrence of each of the lower granularity dimensions (in this case, for Year = 2013):

The commonly suggested fix for this is to use the Aggr without the Sum and with a nodistinct like this:

   Aggr(NoDistinct Sum(Sales), Product, Territory)

At first, this will appear to solve the problem:

The problem occurs when we decide to have a total row on this chart:

Because there is no aggregation function surrounding the Aggr, it does not total correctly at the Product or Territory dimensions.  And we can't add an aggregation function - like Sum - because it will break one of the other totals.

There is, however, something different that we can do - something that doesn't involve Aggr at all!  We can use our old friend Total:

   Sum(Total<Product, Territory> Sales)

This will calculate correctly at all the levels:

---

Stephen Redmond is author of Mastering QlikView, the QlikView for Developer's Cookbook and QlikView Server and Publisher
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

Easy incremental load

I was demoing a quick incremental load script earlier today so I thought I would share it here.

To generate the test data, I used an echo command at the Command Prompt to generate data using the system time:

echo %DATE% %TIME% %RANDOM% >> Data.txt

Every time I run this command it writes a new line to the text file with the current system date and time and a random value.  Perfect for demoing an easy incremental load.

Here is the code:

// vLastLoadTime holds the last date of loading
// If it is not set, then set it to 1,000 days ago
If Len('$(vLastLoadTime)')=0 Then 
Let vLastLoadTime=Num(Now()-1000);
End If 

// vLoadTime holds the current run time
Let vLoadTime=Num(Now());

// Load the data from the data source
// between the two dates
Data:
LOAD @1:23 as DateTime, 
     @24:n as Value
FROM
[..\Data Files\TXTs\Data.txt]
(fix, codepage is 1252)
Where @1:23>$(vLastLoadTime)
And @1:23<=$(vLoadTime);

// If there were any rows,
// concatenate the QVD rows and re-Store
If NoOfRows('Data') > 0 Then

// Get the length of the QVD file
Let vFileLen=FileSize('Data.qvd');

// If the file exists, load it
if Len('$(vFileLen)')>0 Then

Concatenate (Data)
Load
DateTime,
Value
From [Data.qvd] (qvd);

End if

// Store the full table back to QVD
Store Data into [Data.qvd];

// We can drop the table
Drop Table Data;

End if

Let vLastLoadTime=vLoadTime;

// Load the data from the QVD
Data:
Load
DateTime,
Value
From [Data.qvd] (qvd);

Of course, this is just one type of incremental load that we can do using QVDs.  There is an excellent article in the QlikView help file that describes other scenarios.  It is worth reading.

---

Stephen Redmond is author of Mastering QlikView, QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond