Sum till month ignoring month as dimension

[saimahasan]

Hi All,

I need urgent help regarding the problem:

I have count of IDs which i have to show monthwise where month is my dimension. Since i have to show month as horizontal dimensioni have pulled it on the top of pivot chart.

Attached is the sample data

Lets say if for June my count if IDs are 109 and for Aug it is 22. then the chart should show 109 under July and 124 under Aug since the ID may get duplicated in both the months. Hence need to take only distinct count till the month in dimension.

[sunny_talwar]

Once again, I understand that you need accumulation for 1 expression, but without as of table, this expression will be very complex and will impact the overall chart. Whereas, the impact of as of table on your other 9 expression will be very little and will still keep the performance good. Now if you still are hesitant to use it, then I can show you how this can be done if you can provide a sample.... otherwise if you are very reluctant, then may be someone else can help you better.

Best,

Sunny

[saimahasan]

PFA the attached sample and the screenshot of the chart I need.

Also for the expression No.of partner locations I am using --- =Count({<Active_Inact_Flag={'Active'}>} DISTINCT PARTNERID)

and for No of Branches or FCs Geo Mapped I am using  --- =If(IsNull(Before([No of Branches or FCs Geo Mapped])) or Before([No of Branches or FCs Geo Mapped])=0,Count({<Active_Inact_Flag={'Active'},New_Status={'Approved'},VISITED_DATE={"<=$(vMaxDate)"},FIN_MONTH=,FIN_YEAR_LONG=>}DISTINCT PARTNERID),
RangeSum(Before(Count({<Active_Inact_Flag={'Active'},New_Status={'Approved'},FIN_MONTH=,FIN_YEAR_LONG=>}  DISTINCT PARTNERID))) + Count({<Active_Inact_Flag={'Active'},New_Status={'Approved'},VISITED_DATE={"<=$(vMaxDate)"},FIN_MONTH=,FIN_YEAR_LONG=>}DISTINCT PARTNERID))

[saimahasan]

Thanks... I tried with AsOfTable and it worked. But the only problem i am facing is month selection. When i try to select month from m normal calendar it gives me specific months data rather than accumulated data. On the other hand if i try to select from AsOfMonth field by using the formula subfield(AsOfMonth,' ',2) then it gives me proper data.

Please suggest if there is any alternative solution for this.

[sunny_talwar]

So what is the issue with selecting from AsOfMonth field?

[saimahasan]

I need 2 fields Month and Year. Hence i cannot show AsOfMonth field as its year month field.

[sunny_talwar]

Then create AsOfMonth and AsOfYear and use those for selection.... in fact you can build a whole new AsInCalendar and use that for selections....

[sunny_talwar]

Alternatively... you can do this for accumulative expression:

{<Month, AsOfMonth = p(Month), Year, AsOfYear = P(Year)>}