Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am looking to create a chart like this:
Total Number | Jan 2012 | Feb 2012 |
---|---|---|
Gold | 10000 | 8000 |
In Feb 2012, I need to know how many of the gold accounts are also gold account in Jan 2012. In March I need to know the same for Feb. so on and so forth until December
I created a vartiable called vPriorMonth, I do not know how to use it in a set analysis.
Any help is appreciated!
To give you an idea how to discover retention with previous() function:
Input:
LOAD ID,
Period
FROM
D:\Book2.xlsx
(ooxml, embedded labels, table is Sheet1);
Result:
NoConcatenate LOAD ID,
Period,
if(ID=previous(ID) and Period=previous(Period)+1, 1, 0) as Retention
Resident Input
Order By ID, Period;
Drop Table Input;
- Ralf
you would need to create 3 variables called vMaxYear, vMaxMonth and vPriorMonth
vMaxYear = MAX(Year)
vMaxMonth = MONTH(MAX(Date))
vPriorMonth = MONTH(ADDMONTHS(MAX(Date) ,- 1))
and you can write a couple of Expressions using those variables
SUM({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'}>}GoldAmount)
SUM({<Year={'$(vMaxYear)'},Month={'$(vPriorMonth)'}>}GoldAmount)
Maybe that could help
I am not sure how this can help
SUM({<Year={'$(vMaxYear)'},Month={'$(vMaxMonth)'}>}GoldAmount) //this is for the most current month
SUM({<Year={'$(vMaxYear)'},Month={'$(vPriorMonth)'}>}GoldAmount) // this is for the prior month
how I can achive the goal with these formulas?
Hi Alex,
you should consider to use Above() chart inter record function to get the value of the previous month.
- Ralf
You can create 2 list boxes, one for Year and one for Month
and when you select the a Year it becomes the max year and when you select a month from the Month listbox you will have your max month
try it!
Let me clarify the goal. You do not want the difference in total count per month. You want to know how may specific accounts were gold in the last month and are still gold in the current month. For example, if account "A" was gold in both Jan and Feb, that account will be counted. Sort of a "returning customers" thing. A I correct?
-Rob
Rob,
Yes, the was the goal.
Ralf,
I do not think that was what I wanted. I need to get the retention number for the next month.
Would be good to have some sample data..
Maybe this will help a little bit?