How to insert eXP field into my YTD script ?

[paulyeo11]

Hi All

In my load script , i have below script :-

If([Reporting Code]>=100 and [Reporting Code]<=374,'eXP') as [eXP],

And i make use of below expression it work fine for pivot table to display Exp by month :-

Sum({<eXP={"eXP"}>}Amount)/1000

Now i am stuck when i try use of above expression , by Add YTD field , Hope some one can advise me. ( what i need is get the YTD expense amount. )

My imagine expression should be some thing like this , but it does not work :-

=sum(if(YTD and eXP='eXP',Amount))

But it return wrong value.

Paul

[marcus_malinow]

Paul,

do you have a YTD field in your data model? If so, your expression might be:

sum({$<YTD={'1'}, eXP={'eXP'}>} Amount)

If not, then maybe you have a year or date field you can use, in which case your expression might be:

sum({$<Year={'$(=max(Year))'}, eXP={'eXP'}>} Amount)

or

sum({$<Date={">=$(=YearStart(Max(Date)))"}, eXP={'eXP'}>} Amount)

Hope this helps.

Marcus

[paulyeo11]

Hi Marcus

When i try :-

sum({$<YTD={'1'}, eXP={'eXP'}>} Amount)

and

sum({$<Year={'$(=max(Year))'}, eXP={'eXP'}>} Amount)

it return wrong result , ( ya i do have YTD field and LY_YTD field ) , my head get very heavy , and i feel like give up , and want to take a break , suddenly i saw you also give me the last proposal , and i suddenly wake up because the value look good. i think it display for me YTD value.

sum({$<Date={">=$(=YearStart(Max(Date)))"}, eXP={'eXP'}>} Amount)

Now my question is how to modify the above expression , so that it will return me YTD last year value ?

Paul

[marcus_malinow]

Ok,

assuming here that we're ok to use Today() as the basis of our calculation....

sum({$<Date={">=$(=AddYears(YearStart(Max(Date)),-1))<=$(=AddYears(Today(),-1))"}, eXP={'eXP')>} Amount)

or if Date just runs to today...

sum({$<Date={">=$(=AddYears(YearStart(Max(Date)), -1))<=$(=AddYears(Max(Date), -1))"}, eXP={'eXP'}>} Amount)

[paulyeo11]

Hi Marcus

Yes i am okay to use today date for cal.

sum({$<Date={">=$(=AddYears(YearStart(Max(Date)), -1))<=$(=AddYears(Max(Date), -1))"}, eXP={'eXP'}>} Amount)

when i try the above expression , it return wrong value.

Paul

[marcus_malinow]

ok then try this:

sum({$<Date={">=$(=AddYears(YearStart(Today()),-1))<=$(=AddYears(Today(),-1))"}, eXP={'eXP')>} Amount)

[paulyeo11]

Hi Marcus

I have try , it display null value or missing value.

I assume that when i use your expression , i don't need to select month = 10 and year = 2015 right ?

As i am using the expression in QS.

Paul

[marcus_malinow · Accepted solution]

No, you don't need to select anything.

Might be useful if you uploaded a sample application.

[paulyeo11]

Hi Marcus

After i rest for a while , and i make use of my existing SET expression and add the eXP='eXP' and now it work fine.

sum({$<year = {$(=max(year)-1)}, month = {"<=$(=max({<year={$(=max(year))}>} month))"},eXP='eXP')>}Amount/1/1000)

i can have a good night sleep now. Thank a lot

Paul