Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
You need to disregard selection as below in your expression using set analysis -
Sum({<Year =>} Sales)
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)
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
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.
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
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.
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.