
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to Calculate a Month-Over-Month Change
All,
I have a calculated dollar expression that I would like to create a month-over-month percentage change trend for.
My calculated expression is '$ C_Market' which leads to a an average price over a time dimension (SALES_DATE converted to MONTHNAME). Now I'd like to see the percent change in price from one month to the next.
Now I have:
Jan 2011 - $11400
Feb 2011 - $11800
Mar 2011 - $12100
etc.
I'd like to also see:
Jan 2011 - % change from Dec 2010
Feb 2011 - % change from Jan 2011
Mar 2011 - % change from Feb 2011
etc.
Any help would be most appreciated!!
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So "$ C_Market" is the label you gave the expression? This should work, then.
"$ C_Market"/above("$ C_Market") - 1
A weakness is that it requires your chart to be sorted in the correct order, so you kind of need to lock the sort order. It also won't compare to months that are not in your chart, so your first % change will always be null, even if earlier months exist. There are more complicated solutions that avoid those problems, such as creating an AsOf table, but we might as well start simple if using above() is good enough for your requirement.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
So "$ C_Market" is the label you gave the expression? This should work, then.
"$ C_Market"/above("$ C_Market") - 1
A weakness is that it requires your chart to be sorted in the correct order, so you kind of need to lock the sort order. It also won't compare to months that are not in your chart, so your first % change will always be null, even if earlier months exist. There are more complicated solutions that avoid those problems, such as creating an AsOf table, but we might as well start simple if using above() is good enough for your requirement.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
John,
Your suggestion worked perfectly. Thank you!
Larry

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Ok, one more question. Now I want to index all monthly $ C_Market figures against a base period. For example, I want to compare all subsequent months relative to January 2010.
Unfortunately I only have sale date in my underlying data which I'm using the "MonthName" function to so I can group on month.
I'm trying to use set analysis for this but it's not working...right now I have:
"$ C_Market"/({< MonthName = {$("Jan 2010")} >} "$ C_Market")-1
Am I missing something obvious here?
Thanks in advance!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You can't use set analysis without an aggregation function like sum() or max() or only(). You can't use set analysis on a column label. The syntax for the month literal is incorrect. You can only use set analysis on MonthName if you have a MonthName field, which it sounds like you don't have.
Is January 2010 the anchor month for ALL of your data? What about data prior to January 2010? Do you have any? Will this anchor month change? Is it something that you'll change manually, or is it based on a pattern, like January of the previous year?
We may want to use set analysis, but if so, I'll need to see the underlying expression for "$ C_Market" to make it work.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sorry John, bear with me. I'm pretty new to QV. Here is the syntax for my dimension:
=if(SALES_SALES_DATE>=PeriodStart,
if(SALES_SALES_DATE<=PeriodEnd,MonthName(SALES_SALES_DATE),NULL()),NULL())
Here is the syntax for my "$ C_Market" expression:
=avg(if(SALES_PRICE_SCORE_W>=MinScore,
if(SALES_PRICE_SCORE_W<=MaxScore,
if(SALES_SALE_AGE>=MinAge,
if(SALES_SALE_AGE<=MaxAge,
If(SALES_MILEAGE_CLASS = '1',
SALES_PRICE + ((Constant - SALES_MILEAGE)*(MC1)) + ((AgeAssumption - SALES_SALE_AGE)*marketagecoef) + ((marketmsrpass - SALES_MSRP)*MSRPcoef),
If(SALES_MILEAGE_CLASS = '2',
SALES_PRICE + ((Constant - SALES_MILEAGE)*(MC2)) + ((AgeAssumption - SALES_SALE_AGE)*marketagecoef) + ((marketmsrpass - SALES_MSRP)*MSRPcoef),
If(SALES_MILEAGE_CLASS = '3',
SALES_PRICE + ((Constant - SALES_MILEAGE)*(MC3)) + ((AgeAssumption - SALES_SALE_AGE)*marketagecoef) + ((marketmsrpass - SALES_MSRP)*MSRPcoef),
If(SALES_MILEAGE_CLASS = '4',
SALES_PRICE + ((Constant - SALES_MILEAGE)*(MC4)) + ((AgeAssumption - SALES_SALE_AGE)*marketagecoef) + ((marketmsrpass - SALES_MSRP)*MSRPcoef),
If(SALES_MILEAGE_CLASS = '5',
SALES_PRICE + ((Constant - SALES_MILEAGE)*(MC5)) + ((AgeAssumption - SALES_SALE_AGE)*marketagecoef) + ((marketmsrpass - SALES_MSRP)*MSRPcoef), NULL()))))))))))
As you can see, I have quite a few conditions that allow me to control for volatility in the underlying data.
I'm only concerned about price movement after Jan 2010 and relative to Jan 2010. I may manually update this anchor period periodically.
Thanks again!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You don't need the nulls to be explicit in your dimension, you could combine the two terms with an and, and you'd be well served by making a SALES_SALES_MONTH field:
if(SALES_SALES_DATE>=PeriodStart and SALES_SALES_DATE<=PeriodEnd,SALES_SALES_MONTH)
Also, calculated dimensions tend to slow down charts, so if you can actually select the date range based on the period start and end, or simply have some other way of selecting the date range, you could then just use SALES_SALES_MONTH for best performance.
Your expression for $C_Market could also be significantly simplified and the performance improved. First, define a variable with a single parameter ($1) for the common part of the expression:
LET vWhatever = 'SALES_PRICE + (Constant - SALES_MILEAGE)*$1
+ (AgeAssumption - SALES_SALE_AGE)*marketagecoef
+ (marketmsrpass - SALES_MSRP)*MSRPcoef'
Then convert your nested ifs to a combination of set analysis and pick. I think this:
avg({<SALES_PRICE_SCORE={">=$(=MinScore) <=$(=MaxScore)"}
,SALES_SALE_AGE={">=$(=MinAge) <=$(=MaxAge)"}
,SALES_MILEAGE_CLASS={'1','2','3','4','5'}>}
pick(num(SALES_MILEAGE_CLASS)
,$(vWhatever(MC1))
,$(vWhatever(MC2))
,$(vWhatever(MC3))
,$(vWhatever(MC4))
,$(vWhatever(MC5))))
OK, now let's look at what we need to do to calculate that for January 2010. Well, assuming we've added our SALES_SALES_MONTH field, I think we just need to add that to the set analysis:
avg({<SALES_PRICE_SCORE={">=$(=MinScore) <=$(=MaxScore)"}
,SALES_SALE_AGE={">=$(=MinAge) <=$(=MaxAge)"}
,SALES_MILEAGE_CLASS={'1','2','3','4','5'}
,SALES_SALES_MONTH={'Jan 2010'}>}
pick(num(SALES_MILEAGE_CLASS)
,$(vWhatever(MC1))
,$(vWhatever(MC2))
,$(vWhatever(MC3))
,$(vWhatever(MC4))
,$(vWhatever(MC5))))
I'm also wondering if we can put the pick inside of the variable.
avg({<SALES_PRICE_SCORE={">=$(=MinScore) <=$(=MaxScore)"}
,SALES_SALE_AGE={">=$(=MinAge) <=$(=MaxAge)"}
,SALES_MILEAGE_CLASS={'1','2','3','4','5'}
,SALES_SALES_MONTH={'Jan 2010'}>}
$(vWhatever(pick(num(SALES_MILEAGE_CLASS),MC1,MC2,MC3,MC4,MC5))))
I almost certainly have syntax or other errors, unfortunately. It's hard to get everything right on the first pass with an expression this big while rushing and unable to test. But hopefully you'll get the idea.
Also, I suspect you could do even better by making an explicit connection between the SALES_MILEAGE_CLASS and the MCx to use in your data model, perhaps by converting the MCx fields to a single field by changing columns to rows in some table. Very hard to say for sure without knowing your data and data model, though.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Wow! That's a lot of info! It's going to take me some time to digest and implement. Thanks so much for your time John; it's greatly appreciated.
Larry

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I just came accross the same issue and was wondering, if you did solve this issue with John 's suggestion?
I was trying to apply the same logic, but I couldn't get it to work.
Would you mind sharing the expression/ set analysis you are using to calculate this?
Thanks a lot!
Adi
