# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

New Contributor II

## Difficulty with Lift % Expression

I am having an issue calculating a Lift % formula.  Typical lift % formula is something like this (2017 sales - 2016 sales) / 2016 sales.

For this lift % formula in question, I am trying to determine the lift by avg. ticket sales \$

The 1st problem is that I have 2 different locations (Location 'A Retail' and location 'B Retail').  So I cannot simply use a Set Analysis and just use the Avg sales dollars.  That would give me an incorrect figure.  For instance, if the average ticket sale is \$20 in location A and \$22 in location B, then the average ticket when combining *both* locations would be roughly \$21.  But if I used set analysis and used the average function of both places, I would end up getting an incorrect number of \$42.

What I have found works for an expression to come up with avg. ticket \$ is this:

(Sum({\$<Location_Year={"A Retail2017"}>}[Sales Amount]) + Sum({\$<Location_Year={"B Retail2017"}>}[Sales Amount]))  /  (Count({\$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2017"}>}[Trans ID]))

This expression basically sums up the sales from both retail locations and divides the count of tickets (Trans ID) from both locations.

However, now I'm trying to calculate the Lift % of the avg. ticket sales \$ from 2017 vs. 2016.  This is the expression I tried using, but it  keeps giving 0.0% on every metric:

((Sum({\$<Location_Year={"A Retail2017"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2017"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2017"}>}[Trans ID]))

-

(Sum({\$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2016"}>}[Trans ID])))

/

(Sum({\$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2016"}>}[Trans ID]))

I'm guessing that I have the parentheses in the incorrect spot.  If anybody could help, I would appreciate it.

Rich

1 Solution

Accepted Solutions
New Contributor II

## Re: Difficulty with Lift % Expression

Okay, I just figured it out.

Notice the parentheses.  For the margin difference (all of the steps prior to the division)...I start off with 3 parentheses.  At the end of the first part (just before the division symbol), I put it 4 parentheses.

(((Sum({\$<Location_Year={"OIA (A) Retail2017"}>}[Sales Amount])+Sum({\$<Location_Year={"OIA B Retail2017"}>}[Sales Amount]))/(Count({\$<Location_Year={"OIA (A) Retail2017"}>}[Trans ID])+Count({\$<Location_Year={"OIA B Retail2017"}>}[Trans ID])))

-

((Sum({\$<Location_Year={"OIA (A) Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"OIA B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"OIA (A) Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"OIA B Retail2016"}>}[Trans ID]))))

/

((Sum({\$<Location_Year={"OIA (A) Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"OIA B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"OIA (A) Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"OIA B Retail2016"}>}[Trans ID])))

Rich

5 Replies
MVP

## Re: Difficulty with Lift % Expression

May be this with one extra parenthesis to begin with

(((Sum({\$<Location_Year={"A Retail2017"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2017"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2017"}>}[Trans ID]))

-

(Sum({\$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2016"}>}[Trans ID])))

/

(Sum({\$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2016"}>}[Trans ID]))

New Contributor II

## Re: Difficulty with Lift % Expression

Unfortunately that creates an error in the expression.

Rich

MVP

## Re: Difficulty with Lift % Expression

((Sum({\$<Location_Year={"A Retail2017"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2017"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2017"}>}[Trans ID]))

/

(Sum({\$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2016"}>}[Trans ID]))

-

1

New Contributor II

## Re: Difficulty with Lift % Expression

That still gives an error in the expression.  However, I think I started to pinpoint some of the issue.

For example, the 2017 avg. ticket sale comes out to \$10.31 vs. 2016 of \$10.51 since.  The difference is -\$0.19 with the lift supposed to be -1.81%.

When I type in the following expression:

((Sum({\$<Location_Year={"A Retail2017"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2017"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2017"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2017"}>}[Trans ID])))

-

((Sum({\$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2016"}>}[Trans ID])))

It gives me the correct dollar difference (-\$0.19).

However, when I try to then divide that by the 2016 avg ticket price (example below):

/

((Sum({\$<Location_Year={"A Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"A Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"B Retail2016"}>}[Trans ID])))

That is where it doesn't come in correctly.  In fact, the end result is now 930.9% (instead of -1.81%).

When I switched the format over to 'money', it reads \$9.31.  That is \$1 off from the 2017 avg. ticket sales.

In fact, every metric does the same thing...it reads \$1 off from the 2017 avg. ticket sale.

So there must be something going on when I divide the difference by the 2016 avg. ticket sale in the expression.

New Contributor II

## Re: Difficulty with Lift % Expression

Okay, I just figured it out.

Notice the parentheses.  For the margin difference (all of the steps prior to the division)...I start off with 3 parentheses.  At the end of the first part (just before the division symbol), I put it 4 parentheses.

(((Sum({\$<Location_Year={"OIA (A) Retail2017"}>}[Sales Amount])+Sum({\$<Location_Year={"OIA B Retail2017"}>}[Sales Amount]))/(Count({\$<Location_Year={"OIA (A) Retail2017"}>}[Trans ID])+Count({\$<Location_Year={"OIA B Retail2017"}>}[Trans ID])))

-

((Sum({\$<Location_Year={"OIA (A) Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"OIA B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"OIA (A) Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"OIA B Retail2016"}>}[Trans ID]))))

/

((Sum({\$<Location_Year={"OIA (A) Retail2016"}>}[Sales Amount])+Sum({\$<Location_Year={"OIA B Retail2016"}>}[Sales Amount]))/(Count({\$<Location_Year={"OIA (A) Retail2016"}>}[Trans ID])+Count({\$<Location_Year={"OIA B Retail2016"}>}[Trans ID])))

Rich