topic Re: Selecting previous year with current selection in QlikView

Selecting previous year with current selection

Mon, 20 Feb 2017 03:30:24 GMT

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

Re: Selecting previous year with current selection

Anil_Babu_Samineni — Mon, 20 Feb 2017 04:18:32 GMT

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?

Re: Selecting previous year with current selection

Mon, 20 Feb 2017 04:29:39 GMT

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

Re: Selecting previous year with current selection

Anil_Babu_Samineni — Mon, 20 Feb 2017 04:35:48 GMT

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?

Re: Selecting previous year with current selection

jonathandienst — Mon, 20 Feb 2017 05:09:36 GMT

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.

Re: Selecting previous year with current selection

Mon, 20 Feb 2017 05:10:35 GMT

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

Re: Selecting previous year with current selection

Mon, 20 Feb 2017 06:34:17 GMT

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