topic Re: Expression to use for 'Comparable' Locations in App Development

Expression to use for 'Comparable' Locations

msawyercke — Mon, 03 Oct 2016 16:22:30 GMT

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?

Re: Expression to use for 'Comparable' Locations

sunny_talwar — Mon, 03 Oct 2016 16:43:22 GMT

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)

Re: Expression to use for 'Comparable' Locations

msawyercke — Mon, 03 Oct 2016 17:31:03 GMT

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!!

Re: Expression to use for 'Comparable' Locations

sunny_talwar — Mon, 03 Oct 2016 19:05:14 GMT

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)

Re: Expression to use for 'Comparable' Locations

msawyercke — Mon, 03 Oct 2016 19:46:17 GMT

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!

Re: Expression to use for 'Comparable' Locations

msawyercke — Mon, 03 Oct 2016 20:23:18 GMT

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.....

Re: Expression to use for 'Comparable' Locations

sunny_talwar — Mon, 03 Oct 2016 20:31:58 GMT

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

Re: Expression to use for 'Comparable' Locations

msawyercke — Mon, 03 Oct 2016 20:38:12 GMT

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.

Re: Expression to use for 'Comparable' Locations

msawyercke — Mon, 03 Oct 2016 20:47:50 GMT

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

Re: Expression to use for 'Comparable' Locations

sunny_talwar — Mon, 03 Oct 2016 20:48:14 GMT

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

Re: Expression to use for 'Comparable' Locations

msawyercke — Mon, 03 Oct 2016 20:50:48 GMT

I sum CY, then Sum LY, then use the formula (Sum CY/Sum LY)-1

Re: Expression to use for 'Comparable' Locations

msawyercke — Mon, 03 Oct 2016 20:54:31 GMT

Re: Expression to use for 'Comparable' Locations

sunny_talwar — Mon, 03 Oct 2016 21:00:22 GMT

Try this:

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

Re: Expression to use for 'Comparable' Locations

msawyercke — Mon, 03 Oct 2016 21:04:13 GMT

Outstanding!! Thank You, Sunny!!