Calculation on the basis on one yearmonth field

[rsdhavle]

We have one date yearmonth field in the format 201403,201402, 201401 etc. The data get refreshed every month. I need to calculate revenue of latest month follwed by previous months as columns. Column 1 will show revenue for latest, Column 2 will show revenue of previous of that month and so on. Dimension could be client, business etc.

What would be the expressions so that it will fit for continuously changing data and give values accordingly.

[MK_QSL]

Something Like Below?

SUM({<YearMonth = {'$(=Max(YearMonth))'}>}Sales)

SUM({<YearMonth = {'$(=Max(YearMonth)-1)'}>}Sales)

[Nicole-Smith]

Does the solution I gave in this thread not work for you?

getting previous month values from yearmonth column

[its_anandrjs]

Hi,

Write like some thing

=SUM({<[yearmonth ] = {'>=$(=Max([yearmonth])) <=$(=Max([yearmonth ])-1)'}>}SaleValue)

=SUM({<[yearmonth ] = {'>=$(=Max([yearmonth])-1) <=$(=Max([yearmonth ])-2)'}>}SaleValue)

Hope this helps

Thanks & Regards

[shree909]

Hi if you have a year month Table.

try to arrange the Year month table in  descending order.

Create two variable:

Try to do  it in the script;

LET VcurrentYearMonth= Peek('TableName','0', fieldName);

LET vPreviousYearMonth=Peek('TableName','1', fieldName);

  And use  this expression in the set analysis

current YearMonth   =SUM({<YearMonth = {'$(VcurrentYearMonth'}>}Sales)

PreviousYearMonth   =SUM({<YearMonth = {'$(vPreviousYearMonth)'}>}Sales)

So whenever u reload these values are caluculated and displayed.

Thanks