topic Re: HowTo implement a function independent to the filters in QlikView

HowTo implement a function independent to the filters

Fri, 11 Mar 2016 05:37:26 GMT

Hello experts,

I need to implement a function, let’s assume a simple SUM, which works independent to the filters.

Meaning: even if I use a filter, e.g. Year = 2015, I would still see the SalesData_Sales SUM of 2016.

All data is in one / the same table.

The requirement:

I’ve setup an example with all data already in place, so you can play with it.

Re: HowTo implement a function independent to the filters

Fri, 11 Mar 2016 13:17:14 GMT

Hi Nik,

You need to use a Set Analysis.

If I understand you correct your expression becomes SUM({<Year={2015}>}Sales) and SUM({<Year={2016}>}Sales)

Hope This helps.

Re: HowTo implement a function independent to the filters

Digvijay_Singh — Fri, 11 Mar 2016 13:26:06 GMT

You need to disregard selection as below in your expression using set analysis -

Sum({<Year =>} Sales)

Re: HowTo implement a function independent to the filters

maxgro — Fri, 11 Mar 2016 20:22:54 GMT

you can look for "set analysis" in the Qlik help

or here

Why is it called Set Analysis?

Set Analysis: syntaxes, examples

for your .qvw you can start with these expressions

Sum(SalesData_Sales)

Sum( {$<SalesData_Year= {2015}>} SalesData_Sales)

Sum( {$<SalesData_Year= {2016}>} SalesData_Sales)

Sum( {$<SalesData_Year= {$(=Max(SalesData_Year))}>} SalesData_Sales)

Sum( {$<SalesData_Year= {$(=Max(SalesData_Year)-1)}>} SalesData_Sales)

Re: HowTo implement a function independent to the filters

Mon, 14 Mar 2016 13:53:16 GMT

Hi Jasper,

thanks for the good hint.

Now it's getting complicated ... I've tried to solve another requirement with the provided help ... but I'm stuck for 2 days, now ...

Requirement: The Result in the Year 2016 = Sum<Sales in 2015> - Sum<Profit in 2014>

If I use the calculation like this ... the year 2016 will not be visible.

Sum({$<SalesData_Year={2015}>} SalesData_Sales) - Sum({$<SalesData_Year={2014}>} SalesData_Profit)

How can that be handle in qlik? Does anyone know?

I've uploaded the new file to play with.

Regards

Nik

Re: HowTo implement a function independent to the filters

Mon, 14 Mar 2016 14:39:34 GMT

Do you have to do this calculation on multiple years or only on the year 2016?

Because you don't have the data for 2015 and 2015.

Re: HowTo implement a function independent to the filters

Mon, 14 Mar 2016 20:35:13 GMT

Hi,

the real calcualtion is much more complicated.

I've posted a kind of snippet in order to find out how it actually mus be done if I'm in 2016 and I need to take a value from 2015 and 2014.

It seems not to work since the relation to the date of 2014 and 2015 is not available in 2016 ... I guess.

I've tried to setup a flag table ... in that table I try to push values from 2014 & 2015 to 2016... somehow

I cannot manage to creat a table with flags like this (compare with the screenshot above):

SALESDATA_YEAR SALESDATA_SALES FLAG

2014 10,00 € no (can create this record, 10€ are from 2014)

2014 20,00 € no (can create this record, 20€ are from 2014)

2014 30,00 € no (can create this record, 30€ are from 2014)

2016 10,00 € yes (cannot create this record, 10€ are from 2014)

2016 20,00 € yes (cannot create this record, 20€ are from 2014)

2016 30,00 € yes (can notcreate this record, 30€ are from 2014)

This way I hope to get this missing link between the years and then I can do the calculation by using the flag table ... but as I've said ... I cannot create such setting of the table in the load-script.

Do you think it's possible at all?

Reagrds

Nik

Re: HowTo implement a function independent to the filters

Wed, 16 Mar 2016 11:43:32 GMT

Hi Nik,

I don't know how to do this the way you want it. But I think it is possible, I just don't know how.

My best solution would be to calculate the results in a text object for every year. This is probably a one time action every year. therefor it shouldn't be that must work to do it.

Re: HowTo implement a function independent to the filters

Mon, 21 Mar 2016 09:22:30 GMT

Hello,

I've just solved my issue by using flag-tables concept in the load script and further coding in set analysis.

It's a real pity, that qlik cannot hanlde such requirements in set analysis. I hope the framework would be enhanced the required way, someday.

Tahnks to everyone for the help.