Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
denisb
Contributor III
Contributor III

SUM of KPI from "date_last_year" in same line like "date"

Hello, 

I have a fact table like this: 

Column Description
DATE Our normal date
DATE_LAST_YEAR

Specific date from last year, based on this calculation:

date(if(year(DATE)-(round(year(DATE)/4)*4) = 0, num(DATE) - 371, NUM(DATE)-364))

KPI Our KPI

 

Now I want to make a table or chart, which shows me my KPI for each Date and its date_last_year. But it should be in the same row. I try to show my needs with this screenshot: 

denisb_0-1652882846246.png

I don´t know, how to make my measure, to show the KPI from last (specific date) in the right row. 

Maybe someone has an idea? 

 

Thanks a lot. 

Labels (1)
7 Replies
denisb
Contributor III
Contributor III
Author

Also tried this one and does not work: Sum({$<DATE={$(=DATE_LAST_YEAR)}>}KPI)

denisb_0-1652887471741.png

 

vinieme12
Champion III
Champion III

please post some sample data in excel,

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
denisb
Contributor III
Contributor III
Author

Yes of course.

Here it is. 

 

vinieme12
Champion III
Champion III

Main:
LOAD
"DATE",
DATE_LAST_YEAR,
KPI
FROM [lib://AttachedFiles/LAST_YEAR_SAMPLE.xlsx]
(ooxml, embedded labels, table is Sheet1);

Left Join(Main)
LOAD
"DATE" as DATE_LAST_YEAR,
KPI as last_year_KPI
FROM [lib://AttachedFiles/LAST_YEAR_SAMPLE.xlsx]
(ooxml, embedded labels, table is Sheet1);

 

exit script;

 

In Chart

qlikCommunity1.PNG

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
denisb
Contributor III
Contributor III
Author

Thank you. 

I forgot to mention, that I already know this solution. It will be my solution, if I won´t find another in the next days. 

I am looking for something like Set Analysis or Aggr, because I don´t want to double my KPIs in the data model. 

 

vinieme12
Champion III
Champion III

There is no front end only solution for this

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
meghnathshah1
Contributor III
Contributor III

Please create   day& Month dimension in calendar then apply year in the set analysis: ie

Date CY LY

1-May sum({<Year={'2022'}>}qty) sum({<Year={'2021'}>}qty)

Thanks