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.
Not applicable

Date minus one year in table

Hi Experts

I have the following problem:

I creadted a variable "VDateminus1year" with the the current date one year ago.

In a table chart I have the following code: Sum ({<Jahr={$(VaktJahr)}>} ([Rohertrag_aktJ])) giving me the sum of earnings in the current year.

Is it possible to change this code to sum up all earnings last year up to today one year ago, using my variable?

Thank you for your help!

Jan

Tags (1)
1 Solution

Accepted Solutions
qlikrajan
Contributor III

Re: Date minus one year in table

Yes, it is possible. Create variable first and then try the expression below.

=sum({<Date={">=$(vStartPrevYTD)<=$(vEndPrevYTD)"}>}[Rohertrag_aktJ])

Variables:

vStartPrevYTD:=addyears(YearStart(Date ),-1)

vEndPrevYTD:=addyears(max(Date),-1)

7 Replies
hrlinder
Honored Contributor

Re: Date minus one year in table

try  sum({<Jahr={=$(VaktJahr)-1},MyDatefield={<=$(CDateminus1year)}>}Rohertrag_aktJ

first paramter filters the previous year, while second paramter filters from beginning of previous year upto

today - 1 year


qlikrajan
Contributor III

Re: Date minus one year in table

Yes, it is possible. Create variable first and then try the expression below.

=sum({<Date={">=$(vStartPrevYTD)<=$(vEndPrevYTD)"}>}[Rohertrag_aktJ])

Variables:

vStartPrevYTD:=addyears(YearStart(Date ),-1)

vEndPrevYTD:=addyears(max(Date),-1)

Not applicable

Re: Date minus one year in table

Hi Rudolf

I tried the following: Sum ({<Audtragsdatum_aktJ={$(VDateminus1year)}>} ([Rohertrag_aktJ]))

But that only eaquals the date. How do I set the condition < my date in the variable?

Best

Jan

hrlinder
Honored Contributor

Re: Date minus one year in table

try

{<=$(VDateminus1year)}

Not applicable

Re: Date minus one year in table

then ([Rohertrag_aktJ])) has a red line undeneath. thus does not work...

hrlinder
Honored Contributor

Re: Date minus one year in table

I send you an example of my current project, which works

sum

({<AuswahlMonat=,AuswahlJahr=,Berichtsdatum={'>=$(=$(vStartDatum)<=(=$(vEndDatum)))'}>}Kapitalstand)


Not applicable

Re: Date minus one year in table

Thanks you gave me a clue to a solution.

Community Browser