8 Replies Latest reply: Mar 21, 2016 5:22 AM by Nik Bach

# HowTo implement a function independent to the filters

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

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

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

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

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

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

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.

• ###### Re: HowTo implement a function independent to the filters

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

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

or here

Why is it called Set Analysis?

Set Analysis: syntaxes, examples

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)