How to replace GL_CODE=50010 with sTOCK field ?

[paulyeo11]

Hi All

My Table 1 expression work fine :-

Sum(Aggr(RangeSum(
Above(RangeSum(Above(Sum({<year, month , GL_CODE = {50010}>}[Amount]*1), 0, RowNo()))),
-Sum({<GL_CODE = {50010}>}TOTAL Aggr(Sum({<year, month, GL_CODE = {50010}>}[Amount]*1), YearMonth))), (YearMonth, (NUMERIC, DESCENDING))))
/Count(distinct YearMonth)

I have create a field sTOCK using load script :-

If([Reporting Code]>=50010 and [Reporting Code]<=50010,'sTOCK') as [sTOCK],

May i know how to convert the above expression using sTOCK instead of GL_CODE=50010 ?

Paul

[paulyeo11]

My QVF

[sunny_talwar · Accepted solution]

May be this:

Sum(Aggr(RangeSum(

Above(RangeSum(Above(Sum({<year, month, sTOCK = {'sTOCK'}>}[Amount]*1), 0, RowNo()))),

-Sum({<sTOCK = {'sTOCK'}>}TOTAL Aggr(Sum({<year, month, sTOCK = {'sTOCK'}>}[Amount]*1), YearMonth))), (YearMonth, (NUMERIC, DESCENDING))))

/Count(distinct YearMonth)

[paulyeo11]

Hi Sunny

Appreciate your help a lot.

[sunny_talwar]

No problem Paul.

But I am not happy that you have been asking these easy things over the past few months. I would really like you to spend some time and learn simple set analysis. I see a lot of potential in you, but somehow when you keep asking these easy set analysis questions, I see lack of effort. Don't take me wrong my friend, I am more than happy to help you, but simultaneously, I would also like to see that you are able to grab from what you learn here and make improvements.

Finally, I don't want to discourage you from posting your questions here, so please continue to post whatever doesn't get resolved.

Best Regards,

Sunny

[paulyeo11]

Hi Sunny

Yes i will take your word very seriously and i will stop for a while and try to figure out before i move further.

Thank you for your patience with me. I am happy that you let me know this , as it is for my own goods.

Paul

[sunny_talwar]

Great

And remember we are always here for you my friend

Best wishes,

Sunny

[paulyeo11]

Hi Sunny

Thank you very much. Once i have sucessful master SET , i think i owe you dinner when you happen to visit SG or i visit US.

Paul

[sunny_talwar]

Sure thing

[paulyeo11]

Hi sunny

After i read the doc and i watch the few blog and also video.  and i try to use the expression from you on below :-

For YTD sales expression :-

Sum(

{$<year = {$(=Max(year)-0)},

month = {"<=$(=Max({<year={$(=Max(year))},sales = {'*'}>} month))"}>}

sales)

Break down the above expression into 2 part :-

Select max year

{$<year = {$(=Max(year)-0)}

Select max month

month = {"<=$(=Max({<year={$(=Max(year))},sales = {'*'}>} month))"}>}

I further break in to very detail :-

and i look for below 3 component :-

$ =  Identifiers

< > = Modifier

-0  or <5  = Operator

Sum

(

{

$<year =

{

$(=Max(year)-0)

}

,

month =

{

"<=$(=Max(

{

<year=

{

$(=Max(year)-0)

}

,

sales = {'*'}>} month))"

}

>

}

sales

)

and i try to add filter SOURCE={TDSPG} before > , i tested it work.

So i am already 30% success in SET. Thank you for your encouragement,

Paul