Previous months value not getting correctly

[ashis]

Hi ,

In my data model, I have a field in my master calendar call Month Counter , it is define as

Year(Date) * 12 + num(Month(Date)) as [Month Counter] (combination of Year and Month)

I have created variable PreviousMonth as

=max([Month Counter]) -1

Now if I want to get average of xxx of the previous month I put it in the expression as

=avg({1<[Month Counter]={'$(vPreviousMonth)' }>} xxx) 

This is giving me wrong result , if I write the expression as

=avg({<[Month Counter]={'$(vPreviousMonth)' }>} xxx)  , I get null value.

Just to test my month counter I took two text boxes and put expression as

current month = =max([Month Counter])

previous month ==max([Month Counter]) -1 it gives correct output though.

Any idea where I am doing wrong.

[ashis]

I tried but same result.

[sasiparupudi1]

What value you get if you put '$(vPreviousMonth)'in a text box?

[sasiparupudi1]

you could try

avg({<[Month Counter]={'$(=MAX([Month Counter])-1)'}>} xxx)

[ashis]

Hi Sasidhar,

If I use the following syntax

avg({<[Month Counter]={'$(=MAX([Month Counter])-1)'}>} xxx)

I get null value. however I was using same syntax only difference is

avg({1<[Month Counter]={'$(=MAX([Month Counter])-1)'}>} xxx) , then it gives me value. But this value is not correct .

[ashis]

Sunny,

My current month value I compared with excel it is correct but the previous month calculation is not correct. 

Is the formula wrong?

[ashis]

I get numerical value. As it is combination of year and month.

[sunny_talwar]

Don't think so... but I don't really know what you have

[boorgura]

What other selections do you have? Should you be using "1" in the set analysis?

If you have any other selections, other than the date - then Current Month and Prev month expressions are looking at different population.

If the selection of date/month is driven by a different field - may just ignoring that selection in the set expression is a better way to do.

[ashis]

So than how do I calculate previous month . I do have another selection criteria in the filter.

I need to calculate previous month as well as previous to previous month.

Could you please suggest what should I use in the set analysis .

[ashis]

Hi Sunny,

I understand why I am getting wrong value for previous month.

Because I am using this 1 in the set analysis so it is considering all the record set instead of my current selection.

=avg({1<[Month Counter]={'$(vPreviousMonth)' }>} xxx) 

current month = =max([Month Counter])

previous month ==max([Month Counter]) -1 it gives correct output though.

However If I remove 1 from the set analysis and modify the expression as

=avg({<[Month Counter]={'$(vPreviousMonth)' }>} xxx)  , then I get same value as I get for current month that is

=avg({<[Month Counter]={'=max([Month Counter]) '}>} ReportTimeInSeconds)

Do you have any idea why it is giving the same value or anything i am missing here.