topic Dimension in Expression in QlikView

Dimension in Expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a simple bar graph illustrating the following formula:

=(only({<Companyname,Year={$(vYEAR3)}>}[Data])

-only({<Companyname,Year={$(=(vYEAR3)-1)}>}[Data]))

/(only({<Companyname,Year={$(vYEAR3)}>}[Data]))

I am using a fader for the variable.

However, I do have other bar graphs showing data for the dimension year.

How can I use such a dimension in the metioned formula to get graphical results for all available years?

Data looks like the following:

Companyname	Year	Data
aaa	2014	325
aaa	2013	321
aaa	2012	231
bbb	2014	111
bbb	2013	222
bbb	2012	333

Cheers!

Re: Dimension in Expression

Digvijay_Singh — Wed, 09 Mar 2016 12:24:50 GMT

Didn't understand the requirement, Can you elaborate your dimension, measure and share the output image you need to create.

It seems you want to modify the given expression in some way but not sure what exactly you need.

Re: Dimension in Expression

sunny_talwar — Wed, 09 Mar 2016 12:26:13 GMT

What is your dimension here? or are you doing this in a text box object?

Re: Dimension in Expression

Wed, 09 Mar 2016 13:44:37 GMT

That is what I´ve got and happy with:

with bars beeing =only({$<Companyname=xxx>}[data])

with line beeing =avg([data])

In chart properties I have added "year" under dimensions.

I just dont know the general expression to get a value like this for bar 2014:

=(only({<Companyname,Year={2014}>}[Data])

-only({<Companyname,Year={2013}>}[Data]))

/(only({<Companyname,Year={2014)}>}[Data]))

Tried "Max(Year)", but didn´t work...

Re: Dimension in Expression

sunny_talwar — Wed, 09 Mar 2016 13:49:49 GMT

Try this:

(Only({<Companyname>} Data) - Above(Only({<Companyname>} Data)))/Only({<Companyname>} Data)

(Only({<Companyname>} Data) - Below(Only({<Companyname>} Data)))/Only({<Companyname>} Data)

You might have to play around with the sorting, but I think one of these should work

Re: Dimension in Expression

Wed, 09 Mar 2016 14:07:24 GMT

Good work, thanks!

Anyway, one visual problem left.

Referring to the above chart, when I am clicking on one bar that year will show up, clicking on it again I see the whole chart again.

That doesn´t work when using your expressions. It says no data available.

Re: Dimension in Expression

sunny_talwar — Wed, 09 Mar 2016 17:29:25 GMT

May be try this:

(Only({<Companyname>} Data) -

If(Only({<Companyname>} Data) > 0, Above(Only({<Companyname, Year>} Data)))

)/Only({<Companyname>} Data)

Re: Dimension in Expression

Thu, 10 Mar 2016 09:35:06 GMT

(Only({<Companyname>} Data) -

Above(Only({<Companyname, Year>} Data))

)/Only({<Companyname>} Data)

That worked out, thanks!

Re: Dimension in Expression

Thu, 10 Mar 2016 10:15:26 GMT

Well, is that not working for different data?

Like:

(Only({<Companyname>} DataX) -

Above(Only({<Companyname, Year>} DataY))

)/Only({<Companyname>} DataZ)

Re: Dimension in Expression

sunny_talwar — Thu, 10 Mar 2016 10:58:14 GMT

Difficult to say what might be the reason. May be there are more than one values and you need to use Sum instead of Only?

=(Sum({<Companyname>} DataX) - Above(Sum({<Companyname, Year>} DataY))/Sum({<Companyname>} DataZ)