How to auto get LY_YTD not by manually turn on LY_YTD ?

[paulyeo11]

Hi All

I using load script create below field :-

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

I can make use of below expression to get yearly Amount :-

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

I have load script for flag LY_YTD :-

If(Num([TempDate]) >= $(vYearStartLY) and Num([TempDate]) < $(vMonthNowLY), -1, 0) As LY_YTD

if I manually activate eXP and LY_YTD , i can get LY_YTD Amount = -678,468

My Question is how to get LY_YTD by exopression ?

I assume the expression is sone thing like below , how it not work :-

sum(if(LY_YTD,Sum({<eXP={"eXP"}>}Amount)/1000))

Paul

[tamilarasu]

Paul,

Can you upload your application.!!

[paulyeo11]

Hi All

Just now i upload the wrong QV Doc ( Because i use iphone to upload the QV Doc from Dropbox , and Dropbox not yet sync from NB dropbox , that is why wrong QV Doc , which does not explan what i need )

Now i reload again , Hope some one can advise me.

Paul

[paulyeo11]

‌HI All

HOpe some one can advise me how to get LY YTD amt .

PAul

[marcus_malinow]

Paul,

just having a little look. However your data model is a horror (8 synthetic keys, many fields which look like temporary fields / duplicates). This doesn't make anyone's life easy.

Would suggest you read up on some data modelling techniques such as Link Tables / Concatenated Fact Tables..

Also if you need to use multiple fields to link tables, use a composite key.

Marcus

[marcus_malinow]

LY YTD =

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

YTD =

sum({$<eXP={'eXP'}, date={">=$(=num(YearStart(Today())))<=$(=num(Today()))"}>} Amount)

[paulyeo11]

Hi Marcus

Thank you very much for your feedback. No wonder none have try to reply me . Now I know why

I will try to upload another file QVW which is only consist of my GL Table.

By the way I have develop this from sales table and keep expend into other table for last 6 year. Due to when I start I never learn and not it is hard to unlearn . It is just like a tree when it young does not make it straight now have to take time for correction.

Paul

Sent from my iPhone

[paulyeo11]

Hi All

I have update my QV doc , which the data structure is not messy . Hope some one can share with me how to get LT_YTD value.

Paul

[marcus_malinow · Accepted solution]

Paul,

I already replied with some expressions for this. Have you tried them?

LY YTD =

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

YTD =

sum({$<eXP={'eXP'}, date={">=$(=num(YearStart(Today())))<=$(=num(Today()))"}>} Amount)

[paulyeo11]

Hi Marcus

I was about to sleep after i posted my qvw file . when i on my bed , i saw your massage. i wake up and test the expression , it work fine. Now i am no more sleepy. ( may be because i get the answer now ) i will apply the script in my app now , as on monday i need to present the data.

Thank you

Paul