Expression to use for 'Comparable' Locations

[msawyercke]

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?

[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]

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]

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]

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]

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]

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

[sunny_talwar]

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