5 Replies Latest reply: Jan 19, 2016 4:28 PM by Oleg Troyansky

# QlikSense Variable Definition Issue - pls help

Hello,#

I'm a novice to data visualization and am using Qlik Sense Cloud.

I am trying to add two KPI's - one to show "YoY change in Revenue" in £ value and one as a percentage value. Someone told me I need to define variables to do this so I got online and tried to adapt some expressions I thought looked helpful. I am therefore using the following expressions at the moment:

For £ Value:

(Sum({\$<vMaxYr = {\$(=MaxYr)}>} Revenue)-Sum({\$<vMinYr = {\$(=MinYr) Revenue)

For % value

(Sum({\$<vMaxYr = {\$(=MaxYr)}>} Revenue)-Sum({\$<vMinYr = {\$(=MinYr) Revenue)) / Sum({\$<vMinYr = {\$(=Min year(2014))}>})

I'm fairly clueless about the syntax and even how to define a variable in QlikSense- in laymens terms what I'm trying to show with the KPI's is:

Sum(Revenue for this year) - Sum (Revenue for previous year), and

(Sum(Revenue for this year) - Sum (Revenue for previous year)) / Sum (Revenue for previous year)

Also, there will eventually be more than 2 years of data so just wondering if it's possible to create an expression that shows values based on the years selected in a filter pane, for example to see YoY growth stats for 1998 and 1999.

I would be grateful for recommendations to any links / resources that might help.

Best wishes,

• ###### Re: QlikSense Variable Definition Issue - pls help

I can see that you are quite confused with the terminology and the syntax. I'd say some basic education, or at least a book, could help.

Let me try and explain some of the basics. For the YoY comparison, we use Set Analysis (the quirky syntax enclosed in squiggly brackets { }). In the Set Analysis condition, we use filters that we apply to your data fields. For example:

Field = {Values}, or Year = {2016}

Sometimes, we use variables to make the values more flexible. Variables need to be enclosed in so called \$-sign expansions, \$( ... ). For example:

Year = {\$(vMaxYear)}

In turn, variables can be created in the Data Load Script, using the LET statements. For example, somewhere in your Data Load Script, you could add these two statements:

LET vCurrentYear = 2016;

LET vPriorYear     = 2015;

Notice that we usually name the variables beginning with a "v", to differentiate them from fields.

So, with these two variables in place, your YoY formula could look like this (assuming that you already have the Year field in your data set):

Sum({\$<Year = {\$(vCurrentYear )}>} Revenue) - Sum({\$<Year= {\$(vPriorYear)}>} Revenue)

There is a lot more to learn and explain here, but that would make a mini-training class...

cheers.

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

• ###### Re: QlikSense Variable Definition Issue - pls help

Thank you Oleg. You are a scholar and a gentleman.

• ###### Re: QlikSense Variable Definition Issue - pls help

Just notices a few missing brackets in the suggested formula and made the necessary changes - watch out for those!

• ###### Re: QlikSense Variable Definition Issue - pls help

Thanks again, I spotted that and fixed it earlier and KPI's are now working great!

Would this expression also work if I had more than two years of data? For example if I wanted to see YoY change for 6 and 7 years ago?

Regards,

• ###### Re: QlikSense Variable Definition Issue - pls help

Well, if you wanted to see YoY performance based on user selection, then variables wouldn't quite help you, right? You'd have to use another form of a \$-sign expression, something like this:

Sum({\$<Year = {\$(=max(Year))}>} Revenue) - Sum({\$<Year= {\$(=max(Year)-1)}>} Revenue)

Notice the equals sign "=" after the opening parenthesis. This calculation allows you to compare the max available Year with the Year before that.