topic Re: How can I do this type of set analysis expression in the script? in QlikView

How can I do this type of set analysis expression in the script?

jblomqvist — Thu, 16 Jul 2015 12:50:35 GMT

Hello there,

I have the following table:

Data:
LOAD [Client number],
[Tax Network],
[Billing Partner],
[Home BU],
FinancialYear,
FinancialYear-1 as PrevFY,
FinancialYear+1 as NextFY,
Category as FYType,
Revenue,
Hours,

Staff

From

data.qvd file

In the above table I am trying to create the front-end version of this expression below:

If(FinancialYear=2016,Sum({$<FinancialYear={2016}>}Revenue)*(Sum({$<FinancialYear={2015}>}Staff)*0.95)/Sum({$<FinancialYear={2015}>}Revenue)
,If(FinancialYear=2017,(Sum({$<FinancialYear={2017}>}Revenue)*(Sum({$<FinancialYear={2015}>}Staff)*0.95))/Sum({$<FinancialYear={2015}>}Revenue)
,If(FinancialYear=2018,(Sum({$<FinancialYear={2018}>}Revenue)*(Sum({$<FinancialYear={2015}>}Staff)*0.95))/Sum({$<FinancialYear={2015}>}Revenue)
,If(FinancialYear=2019,(Sum({$<FinancialYear={2019}>}Revenue)*(Sum({$<FinancialYear={2015}>}Staff)*0.95))/Sum({$<FinancialYear={2015}>}Revenue)

, Sum(Staff)))))

Does anyone know how this can be achieved in the script with the table and example above please?

Re: How can I do this type of set analysis expression in the script?

prashantbaste — Thu, 16 Jul 2015 13:03:44 GMT

Hi John

Can you please tell against which dimensions you have wrote this expressions?

If [Client Number] is your dimension then you can write it as below -

Load [Client Number],

FinancialYear,

Sum({$<FinancialYear>}Revenue)*(Sum({$<FinancialYear={Chr(39)&(FinancialYear-1)&chr(39)}>}Staff)*0.95)/Sum({$<FinancialYear={Chr(39)&(FinancialYear-1)&chr(39)}>}Revenue)

From data.qvd

Group by

[Client Number],

FinancialYear;

I have not tried it but it should work. Please confirm.

Hope this will be helpful for you.

Regards,

Prashant P Baste

Re: How can I do this type of set analysis expression in the script?

petter — Thu, 16 Jul 2015 14:51:17 GMT

It is not possible to use Set Expressions in a load script. You will have to use Peek() and or Previous() functions to be able to pick out values from a row other than the one you are currently reading in the LOAD-statement.

Re: How can I do this type of set analysis expression in the script?

swuehl — Thu, 16 Jul 2015 14:59:17 GMT

Before you start scripting, start thinking about the granularity you'll need.

An expression in a chart is in general sensitiv to user selections (e.g. in Client Number).

If you want to replicate the chart expression in the script, you would need to use an aggregation grouped by the chart's dimensions, filtered by any condition / set expression used in your chart expression, which can be VERY demanding.

You would need to repeat this for EVERY POSSIBLE user selection that your chart will respond to (except for few cases where your expression is insensitive to user selections. Your above sample is sensitive).

I believe the complexity of your script calculation and execution time will overweigh the benefit of precalculating the values in the script.