Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Date | Acc | Rep | Other Dims | Trxn Amt | YTD |
1/5/2019 | AccA | RepX | XXXX | 25 | 25 |
2/10/2019 | AccA | RepX | XXXX | 75 | 100 |
7/15/2019 | AccA | RepX | XXXX | 80 | 180 |
2/10/2019 | AccB | RepY | YYYY | 20 | 20 |
7/18/2019 | AccB | RepY | YYYY | 40 | 60 |
I need YTD column to be populated for each row.
such as for 5th jan it will give sales from 1st jan to 5th jan
for 2/10/2019 it will give sales from 1st jan to 10 the feb and so on
Please help me on that
Hi,
try this:
RangeSum(Above(Sum([Trxn Amt]),0,RowNo()))
Rangesum will not work that i tried.
I need YTD amout for each date
Why will RangeSum not work? Would you be able to elaborate on the issue you see when you use RangeSum?
i am using this. Can you please help me on the end date part (underlined)syntax?
num(sum({<[Process Date]={">=$(=YearStart(Max([Process Date]))) <=$(=[Process Date]=aggr(nodistinct max([Process Date]),[Firm Name],[Advisory Platform Ind],[TA Alias Rep ID],[Rep First Name],[Rep Last Name],[Rep City],[Rep State Code],[Rep Zip Code],[Acct Num],[TA Prod Code],[Voluntary Tran Desc],[Process Date]))"}
,[Process Month]=>}[Sales Amt]),'#,##0')
What does this expression has to do with RangeSum? I thought you tried an expression with RangeSum which didnot work for you? or are you considering the below expression to be the RangeSum expression?
for rangesum I tried this below:
aggr(rangesum(above( Sum({$<[X529 Special Trade Ind]={'Y'},
[Advisory Platform Ind]-={"=len([Advisory Platform Ind])=0"}>}[Sales Amt]) ,0,rowNo())),[Process Date])
But the range limit we cant set 0 and end limit also we cant take rowno() as it will just sum up with above column irrespective of process date and also after using aggr on rangesum it was not working.
I need YTD amount for each date in each row.
1st jan to 15th july
1st jan to 16th july so on like this. SO range sum will not work.
Now I am using this set analysis. But syntax is not working for end date.
It will be difficult to give you an exact expression, but you do need to use RangeSum here... Set analysis cannot calculate YTD if you have date as one of the dimension... in order to do that, you need to use RangeSum or AsOfTable
Rangesum need to set the range as year start date and end date as that date .
like 5th jan if the date then it will be 1st jan as upper limit and 5th jan as lower limit. so in this case hoe rangesum will work?