Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
paulyeo11
Valued Contributor II

How to insert eXP field into my YTD script ?

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

1 Solution

Accepted Solutions
marcus_malinow
Valued Contributor III

Re: How to insert eXP field into my YTD script ?

No, you don't need to select anything.

Might be useful if you uploaded a sample application.

8 Replies
marcus_malinow
Valued Contributor III

Re: How to insert eXP field into my YTD script ?

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

Re: How to insert eXP field into my YTD script ?

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

Re: How to insert eXP field into my YTD script ?

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

Re: How to insert eXP field into my YTD script ?

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

Re: How to insert eXP field into my YTD script ?

ok then try this:

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

paulyeo11
Valued Contributor II

Re: How to insert eXP field into my YTD script ?

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

Re: How to insert eXP field into my YTD script ?

No, you don't need to select anything.

Might be useful if you uploaded a sample application.

paulyeo11
Valued Contributor II

Re: How to insert eXP field into my YTD script ?

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

Community Browser