Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have a Table. it give the correct value. which i select only 1 SOURCE
For example SOURC = ID
But when i want to view the report by all SOURC , it return the wrong value.
Paul Yeo
.
Perhaps this?
Money(Sum(Aggr(If(Match(SOURCE,'TDSID'),money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK))), SOURCE)),$(vMoneyFormatK))
Perhaps this?
Money(Sum(Aggr(If(Match(SOURCE,'TDSID'),money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK))), SOURCE)),$(vMoneyFormatK))
wow , you are scripting expert. Thank you very much. it help me a lot.
Hi Sir
I apply your script into my table 1 , it work fine for SOURCE=ID.
Now i try to change SOURCE = PG , it does not work.
Can you pls help me add another condition ?
As i have try it does not work :-
Money(Sum(Aggr(If(Match(SOURCE,'TDSID'),money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),
Money(Sum(Aggr(If(Match(SOURCE,'TDSPG'),money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK))), SOURCE)),$(vMoneyFormatK))
currently, I don't have app in hand. So i can't test. Perhaps this as assumption?
Pick(Match(SOURCE, 'TDSID', 'TDSPG')+1,
Money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSID'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSPG'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)))
I setup the colors on each to better understand For Others For TDSID For TDSPG
Hi Anil,
Wow such a complicated script , you can make it work with out testing . I try to add one more SOURCE = TDSKL , it does not work , where i go wrong ?
Pick(Match(SOURCE, 'TDSID', 'TDSPG', 'TDSKL')+1,
Money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSID'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSKL'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSPG'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK))))
Because, you are varying it to each from different sector to different matching. So, How Pick(Match(...)) functionality works from Left-Right. In your way, you are having different name and you are calculating with different Manner. For a while try this?
Pick(Match(SOURCE, 'TDSID', 'TDSPG', 'TDSKL')+1,
Money(Sum({<$(ColumnDim901)={$(ColumnDim901)}>}Amount)/$(Columndim89)*1/1000,$(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSID'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSPG'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)),
Money(RangeSum(Above(Sum({<$(ColumnDim901)={$(ColumnDim901)}, SOURCE = {'TDSKL'}>}Amount)/$(Columndim89)*1/1000, 0, Rowno(TOTAL))), $(vMoneyFormatK)))
Hi Anil
Your last expression , you make it very easy for me to add new field like TDSKL. Thank you very much.
Paul Yeo