9 Replies Latest reply: Oct 23, 2015 9:45 AM by Yeo Poh sai

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

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

Paul,

• ###### Re: How to auto get LY_YTD not by manually turn on LY_YTD ?

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 )

Paul

• ###### Re: How to auto get LY_YTD not by manually turn on LY_YTD ?

HI All

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

PAul

• ###### Re: How to auto get LY_YTD not by manually turn on LY_YTD ?

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

• ###### Re: How to auto get LY_YTD not by manually turn on LY_YTD ?

LY YTD =

YTD =

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

• ###### Re: How to auto get LY_YTD not by manually turn on LY_YTD ?

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

• ###### Re: How to auto get LY_YTD not by manually turn on LY_YTD ?

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

• ###### Re: How to auto get LY_YTD not by manually turn on LY_YTD ?

Paul,

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

LY YTD =

YTD =

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

• ###### Re: How to auto get LY_YTD not by manually turn on LY_YTD ?

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