Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
msawyercke
Creator
Creator

Expression to use for 'Comparable' Locations

Good Morning!

I load data for each of our locations that contains sales performance.  It is structured like this:

Unit #     

Current Year Sales ("CY TP 13")   

Year Ago Sales   ("PY TP 13") 

From there, I use the following formula to highlight % Change Vs Ya

AVG ([CY TP 13])/avg([PY TP 13])-1

The problem is, I need to create a chart (perhaps a TreeMap), that only displays the data of 'comparable' locations, since some locations that are reporting sales this year weren't open last year, distorting the % change statistic.  In other words, I want to calculate locations where PY TP 13>0.

In my example, what would the expression be in the expression editor?

Thanks for any help that can be provided?

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Sum({<[Comp_Flag] = {1}>} [CY TP 13])/Sum([PY TP 13]) - 1

View solution in original post

13 Replies
sunny_talwar

Check this link out and see if this helps (have not dig deeper, but it seems that this might help you)

Like For Like (Comparative) Sales Analysis (Flag Method)

msawyercke
Creator
Creator
Author

Thanks, Sunny.  I think you are on the right track...

So, I created the following following phrase in the script editor:

IF ("PY TP 13" >0, 1, 0) as Comp_Flag

But, now in the Expression editor in the chart, I use this expression, and there is an error somewhere:

AVG [Comp_Flag](([CY TP 13])/avg([PY TP 13])-1)

Do you see anything wrong with my approach or expression?

Many, Many thanks!!

sunny_talwar

I am not sure if the flag itself is right or not, but may be you can use it like this:

Avg({<[Comp_Flag] = {1}>} [CY TP 13])/Avg([PY TP 13])-1)

msawyercke
Creator
Creator
Author

I think this worked, Sunny.  I still need to verify the results, but at least the syntax is correct and it is making the calculations and displaying the graph.  I just need to validate the results on this end.

Thanks - have a S U N N Y day!  

msawyercke
Creator
Creator
Author

I guess this solution doesn't work after all:

Avg({<[Comp_Flag] = {1}>} [CY TP 13])/Avg([PY TP 13])-1

The % change for a small group of 9 locations I tested gave me incorrect results.....

Can anyone please help me?

Thanks.....

sunny_talwar

I am sure willing to, would it be possible to share a sample?

msawyercke
Creator
Creator
Author

Sure I will, Sunny.  You are so kind again....

The "correct" % change should be +1.9%.  In my bar graph, it shows up as 30%+

Let me know if you need anything else.

msawyercke
Creator
Creator
Author

And here is what the graph displays when I select the market for that group of locationsCapture.JPG:

sunny_talwar

How are you calculating 1.9% here? are you doing Average of % Change?