Sum Trends

[alanmcgrath]

Hi All,

I'm trying to sum or count trends within a data set.  I have a table with the Dimension Client_# and the expressions: 1.Savings value for the current month for each client- Sum(Savings).  2.Savings value for last month for each client - Sum({<Report_Mo={'$(vPreviousMonth)'}>}Savings). 3. Comparison of the first two - If(Last Month savings is less than this month, 'Trend up', If Last month savings is greater than this month, 'Trend Down', 'Trend Even'.

All that works fine, but now I want 3 text objects which will display the count of clients Trending up, Trending Down, and Trending Even.

Seems like a simple thing but I'm just stuck.

Any help with this is appreciated.

Thanks!

Client_#	Last MonthSavings	Current Month Savings	Trend
Client 1	3	2	Trend Down
Client 2	3	3	Trend Even
Client 3	1	1	Trend Even

[datanibbler]

Hi Alan,

I guess the simple solution

>> COUNT({$<Trend = {'Trend Down'}>} Client_#) <<

won't work? Else I guess you would already have done it.

Can you specify why it won't work or what error_message you have, if any, or so?

Best regards,

DataNibbler

[cheburashka]

Hello Alan.

In the textbox you can't refer to to the straight table that you have build.

Or you can try to rebuild a virtual table in the expression of the textbox and do a count of your Trend field for a particular value. ( Check out the Aggr function on how to build the "virtual table" )

Or you can precalculate the Trend field in the script

, KR Koen

[alanmcgrath]

DataNibbler - I didn't try that.  Trend is not a field in my database but rather calculated, can I still reference it in Set Analysis this way?

My Table works fine, but when I try to do each of the counts, I get a blank.

Koen - thanks, I'll look into the "virtual table"

[datanibbler]

No, you can't - Koen said it, I forgot: You can refer to the expressions used in your table only within the table, not in separate objects - there you'd have to re-calculate the trend, but that should not be too hard, you can still use set_analysis, sth. like this

>> COUNT({$<[Current month savings] = {"$(= '<' & [Last month savings])"}>} Client_#) <<

[alanmcgrath]

Not sure I'm doing it correctly,I plugged in my expressions in place of Current Month Savings and Last Month Savings.@ I'm using the below:DataNibbler

COUNT({$<Savings = {"$(= '<' & Sum({<Report_Mo={'$(vPreviousMonth)'}>}Savings))"}>} Client_#) 

This produces a count of 91 which is the total of Clients

[jaimeaguilar]

Hi,

please check the attached example. You can use this formulas in your text boxes:

='Clients w/ Trend Down: ' &

count({$<Client_#={"=sum({$<MonthName={'Aug 2014'}>} Savings)/sum({$<MonthName={'Sep 2014'}>} Savings)>1"}>} DISTINCT Client_#)

within set analysis you compare if previous month savings / current savings are either >, = or < than 1. (these 3 cases will represent down, even and up trend). So in every text box you count the number of clients that fall into any of these categories.

In my example, for sake of simplicity I used static set analysis for months, you'll just need to substitute that part with your $(vPreviousMonth) variable,

regards

[alanmcgrath]

Awesome, Super helpful!!  Thats working for the most part...just one more wrinkle.  There are 4 values for savings 0,1,2,3.  It's not counting those with a 0 in one of the months because it gives an error when dividing by 0. So if this month was 3 and last month was 0 it should be counted as trend up but its skipping over that.  Will an IF statement help to count those as well or would you suggest a different approach?

Thanks!

[jaimeaguilar · Accepted solution]

You can avoid the division by zero issue modifying the formula like this:

'Clients w/ Trend Down: ' &

count({$<Client_#={"=sum({$<MonthName={'Aug 2014'}>} Savings)  > sum({$<MonthName={'Sep 2014'}>} Savings)"}>} DISTINCT Client_#)

'Clients w/ Trend Even: ' &

count({$<Client_#={"=sum({$<MonthName={'Aug 2014'}>} Savings) = sum({$<MonthName={'Sep 2014'}>} Savings)"}>} DISTINCT Client_#)

'Clients w/ Trend Up: ' &

count({$<Client_#={"=sum({$<MonthName={'Aug 2014'}>} Savings) < sum({$<MonthName={'Sep 2014'}>} Savings)"}>} DISTINCT Client_#)

this way you just compare both sums without having to perform the division and it will count even those cases where current month savings are zero

[cheburashka]

Hello Jaime,

Awesome solution.

Tell me if i'm wrong, you are using an advanced search expression in your set-analysis to set the values of the [Client_#] field?

Advanced search expression:

"sum({$<MonthName={'Aug 2014'}>} Savings)/sum({$<MonthName={'Sep 2014'}>} Savings)>1"

,KR Koen