INDEX

1. Load your variables in from an Excel spreadsheet

2. Create an aged profile from a single date field (for use in such as an aged debt report

1. Load variables from an Excel spreadsheet straight into Qlik

Excel can be very useful when you have a lot of variables in your model.  Some more advanced excel users also prefer this route to help them write Qlik variables, as you can use variables within variables and use excel concatenation to create complex strings.

a) create the excel sheet with the following headers -> Name, Expression, Comment.  Also rename the tab of the sheet as Variables and save the file (example used saved as Finance_Variables.xlsx)

See this downloadable example – Finance_Variables

Once you have this setup and you have a Data connection to the directory in which it is contained, simply select the data or write the code to load this data into the model, into a new section;

//load the expressions from Excel
Expressions:
LOAD
Name,
Expression,
“Comment”
FROM [lib://MAPPING TABLES/Finance_Variables.xlsx]
(ooxml, embedded labels, table is Variables);

Now the data is in we need to loop through it to create the variables….using the following code below the excel data load

//loop thrugh table to create the usable variables

Let vNumberOfRows = NoOfRows (‘Expressions’);
For vI = 0 to (vNumberOfRows – 1)
Let vVariable_Name = Peek (‘Name’, vI, ‘Expression’);
Let [$(vVariable_Name)] = Peek (‘Expression’, vI, ‘Expression’);
Next

That’s it – Load your data and your variables are then ready for use in the dashboard expressions!!

2. Creating an ageing profile:

This can be really useful when you want to use a straight table to create reports that show an aged profile of a balance, as with such as an aged debt report

Based on the field ‘CREATEDDATETIME’

LET vTable=’TABLE_NAME’;

LET vPrefix = upper(replace( ‘$(vTable)’,’_’,’ ‘))

today() – date(floor(CREATEDDATETIME)) AS [$(vPrefix) Age (Days)]

If( [$(vPrefix) Age (Days)] <0, dual(‘Forward Dated’,1),
If([$(vPrefix) Age (Days)] >=0 and [$(vPrefix) Age (Days)] <=7, dual(‘0-7 Days’,2),
If([$(vPrefix) Age (Days)] >7 and [$(vPrefix) Age (Days)] <=14, dual(‘8-14 Days’,3),
If([$(vPrefix) Age (Days)] >14 and [$(vPrefix) Age (Days)] <=21, dual(’15-21 Days’,4),
If([$(vPrefix) Age (Days)] >21 and [$(vPrefix) Age (Days)] <=28, dual( ’22-28 Days’,5),
If([$(vPrefix) Age (Days)] >28 and [$(vPrefix) Age (Days)] <=60, dual( ‘1-2m’,6),
If([$(vPrefix) Age (Days)] >60 and [$(vPrefix) Age (Days)] <=90, dual( ‘2-3m’,7),
If([$(vPrefix) Age (Days)] >90 and [$(vPrefix) Age (Days)] <=180, dual(‘3-6m’,8),
If([$(vPrefix) Age (Days)] >180 and [$(vPrefix) Age (Days)] <=365, dual(‘6-12m’,9),
If([$(vPrefix) Age (Days)] >365 , dual(’12m+’,10),

‘ ‘))))))))))

AS [$(vPrefix) Age Group],