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.

Not applicable

Selecting previous year with current selection

Dear experts,

I have a question to ask regarding how I can select a previous year selection with a current set analysis selection which i have already made. Having looked and tried other solutions on the forum they were specific for the problem and I could not find a general solution where I was able to use, can anyone please help me out with this issue?

My code is as of such,

Sum({$<BDF_Type={"*Revenue*"}> * $<[fiscal year]-1> }cash)

What I am trying to achieve is to select all the BDF type with the characters revenue as well as the previous fiscal year selection. The reason why i used the " * "  because I thought it would be a union set, but I am very sure that syntax is used wrongly in this context.

The fiscal year date format is given as 2010,2011,2012 etc.

Kind regards,

Royce

1 Solution

Accepted Solutions
jontydkpi
Not applicable

Re: Selecting previous year with current selection

This statement is syntactically and logically correct in the set analysis -  [fiscal year] = {'$(=Max([fiscal year])-1)'}

I expect your required expression looks like:

=Sum({<BDF_Type = {"*Revenue*"}, [fiscal year] = {"$(=Max([fiscal year])-1)"}>} cash)

(sum cash where BDF_Type starts with Revenue having a fiscal year one less than the largest value)

If that does not work, then something else is going on (such as fiscal date extending into the future), so I suggest that you post a small sample qvw to illustrate the problem.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
6 Replies
loveisfail
Not applicable

Re: Selecting previous year with current selection

Can you try like below then we can sort it out if values are generated

Sum({$<BDF_Type={"*Revenue*"} >} cash) * Sum({$<[fiscal year] = {'$(=Max([fiscal year])-1)'}> }cash)

// By above expression, I am showing for cash data points where Search string is Revenue and the Cartesian with Last Year(i.e., 2011). Is this you are expecting?

Life is so rich, and we need to respect to the life !!!
Not applicable

Re: Selecting previous year with current selection

I have tried that expression however it gives a 0 value I do believe the second part of the syntax is incorrect.

{'$(=Max([fiscal year])-1)'}> } the ' ' syntax seems to be making the function =Max into a string or something

loveisfail
Not applicable

Re: Selecting previous year with current selection

It should work if your Fiscal Year has data points. Any how. Is that same you are looking because you have mentioned [Fiscal Year]-1 Here what is your intend to show

Would you provide sample, Please?

Life is so rich, and we need to respect to the life !!!
jontydkpi
Not applicable

Re: Selecting previous year with current selection

This statement is syntactically and logically correct in the set analysis -  [fiscal year] = {'$(=Max([fiscal year])-1)'}

I expect your required expression looks like:

=Sum({<BDF_Type = {"*Revenue*"}, [fiscal year] = {"$(=Max([fiscal year])-1)"}>} cash)

(sum cash where BDF_Type starts with Revenue having a fiscal year one less than the largest value)

If that does not work, then something else is going on (such as fiscal date extending into the future), so I suggest that you post a small sample qvw to illustrate the problem.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Selecting previous year with current selection

You are right, I had a inline statement for fiscal year and used the original year set as the one with data points now it works perfectly! cheers

Not applicable

Re: Selecting previous year with current selection

Cheers for the input once again !! it really helped. May I please also ask if it is possible to have

(sum cash where BDF_Type starts with Revenue having a fiscal year one less than the CURRENT SELECTED VALUE)?


kind regards,

Royce