topic Re: variable in set analysis for dynamic change in QlikView

variable in set analysis for dynamic change

Emil_bros — Fri, 18 Oct 2019 12:18:08 GMT

Hi,

I need to find a formula where variable 'VERSION' changes dynamically in relation to given month.

In my case names of VERSIONS are corresponding to MONTH names (formated as data)

My formula is not working:

sum({<VERSION = {'=Month'}>} VAL)

As a result I want the formula to take value for EACH MONTH from different VERSION

Re: variable in set analysis for dynamic change

sunny_talwar — Fri, 18 Oct 2019 12:34:39 GMT

Not entirely sure, but may be this

Sum({<VERSION = p(Month)}>} VAL)

Re: variable in set analysis for dynamic change

Emil_bros — Fri, 18 Oct 2019 12:41:02 GMT

not working, error in expression

Re: variable in set analysis for dynamic change

sunny_talwar — Fri, 18 Oct 2019 13:00:38 GMT

Sorry this

Sum({<VERSION = p(Month)>} VAL)

Re: variable in set analysis for dynamic change

Emil_bros — Fri, 18 Oct 2019 13:10:55 GMT

thank you Sunny however now the result is a sum of all versions (please see the attached)

Re: variable in set analysis for dynamic change

Emil_bros — Fri, 18 Oct 2019 13:13:03 GMT

should be what is marked blue in attached file

Re: variable in set analysis for dynamic change

sunny_talwar — Fri, 18 Oct 2019 13:40:43 GMT

Set analysis won't work for this... try this

Sum(If(VERSION = Month, VAL))

Re: variable in set analysis for dynamic change

Emil_bros — Mon, 21 Oct 2019 07:12:09 GMT

thank you it now works however I wasn't exactly clear about the data model.

I already use set analysis in the expression to calculate value:

sum({<COST1 = {'Net Sales'}>} VAL)

so to use your formula properly I need to have COST1 filter selected on "Net Sales" which is impacting my other expressions.

Is there any other way?

Re: variable in set analysis for dynamic change

sunny_talwar — Mon, 21 Oct 2019 11:39:41 GMT

So, you want to add the set analysis in this new expression? Try this

Sum({<COST1 = {'Net Sales'}>} If(VERSION = Month, VAL))

Re: variable in set analysis for dynamic change

Emil_bros — Mon, 21 Oct 2019 11:55:39 GMT

Perfect! Exactly what I need. Thank you very much

Re: variable in set analysis for dynamic change

Emil_bros — Tue, 10 Dec 2019 07:02:39 GMT

Hi Sunny,

now I need to implement this solution to another report. Despite using same logic I can't get correct result.

I have desctibed issue inder this link. Can you kindly take a look?

https://community.qlik.com/t5/QlikView-Creating-Analytics/dynamic-variable-in-expression/m-p/1655524#M47820

Emil