topic Re: previous and actual selected year values in QlikView

previous and actual selected year values

eva_maria — Wed, 25 Nov 2020 16:16:04 GMT

In my attached file, i've a chart (Points) where I show values corresponding to the previous year to the selected one.

I also need to show (on the same chart) the selected year values for Comm and value fields.

I appreciate any help!

Re: previous and actual selected year values

Sergey_Shuklin — Mon, 20 Aug 2018 10:21:58 GMT

Hello!

Please, find a green table in the attachement. Is this the result you've wanted to achieve?

Re: previous and actual selected year values

tresB — Mon, 20 Aug 2018 10:32:49 GMT

Not sure if understood right. To see, data for current selection (year) and last to current selection year, you may try using exp, like:

Sum({$+<Year={$(=(Year)-1)}>}Value)

Re: previous and actual selected year values

eva_maria — Mon, 20 Aug 2018 12:11:05 GMT

Sergey, your reply is perfect. Please, could you explain me how does it work the expresions with pick, match, and

ValueList?

What's the different between $(=(Year)-0) and $(=(Year))?

Thanks for your invaluable help!!

Regards

Re: previous and actual selected year values

eva_maria — Mon, 20 Aug 2018 12:23:24 GMT

Hi Tresesco. Thanks for be willing to help and try to solve my question.

I've seen your file and it really does not fit my question (may be my own fault) but your expresision also show the selected year and i only have to show previous selected year with selected year values in another column.

Regards!!

Re: previous and actual selected year values

Sergey_Shuklin — Mon, 20 Aug 2018 12:57:15 GMT

Glad to help 😃

ValueList() creates a field with some values, it's often used for creating a dimensions.

If we have numeric values like ValueList(1,2,3) then we can use Pick() for setting an expression values for it:

=pick(

valuelist(1,2,3)

,sum(sales)

,count(orders)

,count(customers)

)

But, if we have a string values such as ValueList('jan', 'feb', 'mar') than we can't use Pick() function directly, we should use Match() function for support:

pick(

match(

valuelist('jan', 'feb', 'mar')

,'jan'

,'feb'

,'mar'

) //will return 1,2,3 respectively

,sum({<mon={'jan'}>}sales)

,sum({<mon={'feb'}>}sales)

,sum({<mon={'mar'}>}sales)

)

In your case I've created one valued field with ValueList(Year) and when a user selecting some year the value of ValueList(Year) is changing.

Then all I've to do is set different expressions for different calculations (Y-0 and Y-1, Point and Values).

And there no difference between Y - 0 and Y, it's the matter of taste 😃

Hope I didn't explane it confusedly.

Re: previous and actual selected year values

eva_maria — Tue, 21 Aug 2018 06:18:11 GMT

Sergey, Thanks for sharing your knowledge. I'lll try to assimilate your explanation.

Regards!!

Re: previous and actual selected year values

eva_maria — Wed, 22 Aug 2018 11:08:40 GMT

HI Sergey! one more question

Changing the year on the imput box on my real application, the following message appears

"Error in expression: All parameters to ValueList must be constant"

On my application i have three tables linked by year field

Do you know any reason??

Regards.

Re: previous and actual selected year values

Sergey_Shuklin — Wed, 22 Aug 2018 11:18:41 GMT

Hello, Eva!

If you wish to use an inputbox you should replace 'Year' field in ValueList(Year) with your variable. And this replacement should be done in both dimension and expressions. Please, find the attached example.

Re: previous and actual selected year values

eva_maria — Wed, 22 Aug 2018 11:43:34 GMT

Sorry I have not expressed myself correctly. I wanted to say list box.

The funny thing is that sometimes it fails and sometimes it does not

thanks for your patience!!

Re: previous and actual selected year values

Sergey_Shuklin — Wed, 22 Aug 2018 12:07:33 GMT

Such error means you have more than one value if you selecting some years. You can try to use a variable where you can check that only one value of Year field is selected and then return it to ValueList() function. If it won't help we will make it with another way.

Re: previous and actual selected year values

eva_maria — Wed, 22 Aug 2018 12:47:25 GMT

Thanks Sergey, Problem solved

Regards