Set Analysis with Date Range

[kaldubai]

Hey all,

i have been having this problem and i am kind of stuck here and would really appreciate some tips on the matter.

so i have come up with a formula which calculates the QTY from a selected date and backward "< ="  and shows correct figure. Now what i need is to show the same but monthly wise.

For Example :

if the user selects 12/31/2018

the developed formula shows   -972,286.26  which is correct 

the formula used 

"Sum({<DT_2={"<=$(v_AsAtDate) "},DT_1=,Month=,Year=,Day=,[MTL.TRANSACTION_TYPE_ID]={21,62},GROUP_FLAG={1},ORG_TYPE={'*'}
,[MTL.STOCK_FLAG]={'ONHAND'}>}[MTL.PRIMARY_QUANTITY] )

"

now when i distribute that over months it gives me only for that specific month as below  which is not correct 

Current

it should take the last day of each month and backward just like below

below is my data model 

i hope i was articulate enough to convey the issue and thanks in advance 🙂

[Channa]

now the values you seeing in the wrong table is for the Last date of the month or it is for the entire month??

[kaldubai]

yes you are right it shows the figure only for that entire month 

[mato32188]

Hi Kaldubai,

try something like:

rangesum(above(sum({<Month = {"<=$(=max(Month(Date)))"}>}EXPRESSION),0,NoOfRows(TOTAL)))

Your expression should look like:

rangesum(above(Sum({<DT_2={"<=$(v_AsAtDate) "},DT_1=,Month=,Year=,Day=,[MTL.TRANSACTION_TYPE_ID]={21,62},GROUP_FLAG={1},ORG_TYPE={'*'}
,[MTL.STOCK_FLAG]={'ONHAND'}, DATEUSERSELECTS = {<  DATEUSERSELECTS = {"<=$(=max(Date))"}>}>}[MTL.PRIMARY_QUANTITY] ),0,NoOfRows(TOTAL)))

BR

Martin

[Channa]

sorry i am trying to understand

now you want to see only for the last date of the month value?

[sunny_talwar]

How exactly are you getting these numbers?

For example, how are you getting -952,791 for Nov? Can you elaborate on this?

[kaldubai]

@Channa  appologies for the late replay 

i am taking whatever is below or equal to the last day of each month ..in another word i need to take whatever is in between the min date and the last date of each month 

assuming the min date is 1/1/2010

so the monthly distribution for 2018 should be as follow 

Jan >> 1/1/2010 to 31/1/2018

feb>> 1/1/2010 to 28/1/2018 and so forth 

[kaldubai]

@sunny_talwar  apologies for the late replay 

i am reading from an ERP Table where date and transaction id is my only prameter .

the formula used gives me the correct figure <<as on that date and backward >>

"Sum({<DT_2={"<=$(v_AsAtDate) "},DT_1=,Month=,Year=,Day=,[MTL.TRANSACTION_TYPE_ID]={21,62},GROUP_FLAG={1},ORG_TYPE={'*'}
,[MTL.STOCK_FLAG]={'ONHAND'}>}[MTL.PRIMARY_QUANTITY] )

now i want to be able to show the same but per month as mentioned to @Channa 

[kaldubai]

@mato32188  thanks alot for your response, but range sum  is not working for me .