Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

paulyeo11
Valued Contributor II

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

1 Solution

Accepted Solutions
marcus_malinow
Valued Contributor III

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 =

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)

9 Replies

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

Paul,

Can you upload your application.!!

paulyeo11
Valued Contributor II

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 )

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

Paul

paulyeo11
Valued Contributor II

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

marcus_malinow
Valued Contributor III

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

marcus_malinow
Valued Contributor III

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

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
Valued Contributor II

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

paulyeo11
Valued Contributor II

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

marcus_malinow
Valued Contributor III

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 =

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
Valued Contributor II

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