Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
MikeMac84PL
Contributor II
Contributor II

Only(Year)-1) with Set Analysis (Aggr function) Problem

Hello everyone,

I have a problem with showing data in chart - straight table where I wanna show year to year messure.

Dimension = Month;

Expressions =

Only(Year)

sum(Aggr((sum({<Year={$(=Only(Year))}>}[PZ Poz. Wartość po korekcie])/Count({<Year={$(=Only(Rok))}>}distinct [ZAMD NR])),[Dostawca Skrót],[PZ Nr mag.],[PZ Symbol],[Data],[Indeks Linia],[Indeks Indeks] ,[Indeks Nazwa],[PZ Poz. Cena po korekcie],[KH_ID])) - and that's work fine

The problem begins with previous Year:

Only(Year)-1)

sum(Aggr((sum({<Year={$(=Only(Year)-1)}>}[PZ Poz. Wartość po korekcie])/Count({<Year={$(=Only(Year)-1)}>}distinct [ZAMD NR])),[Dostawca Skrót],[PZ Nr mag.],[PZ Symbol],[Data],[Indeks Linia],[Indeks Indeks] ,[Indeks Nazwa],[PZ Poz. Cena po korekcie],[KH_ID])) 

There are no results 😞

Please help

Thanks. 

 

1 Solution

Accepted Solutions
MikeMac84PL
Contributor II
Contributor II
Author

=sum({<Rok={$(=Only(Rok)-1)}>}Aggr((sum ({<Rok={$(=Only(Rok)-1)}>}[PZ Poz. Wartość po korekcie])/Count({<Rok={$(=Only(Rok)-1)}>}distinct [ZAMD NR])),[Dostawca Skrót],[PZ Nr mag.],[PZ Symbol],[Data],[Indeks Linia],[Indeks Indeks] ,[Indeks Nazwa],[PZ Poz. Cena po korekcie],KH_ID))/ if(vWaluta=1,vEUR,1)

 

THATS WORKING... 🙂 

View solution in original post

7 Replies
sunny_talwar

Have you made a selection in a year field? Try this

Sum({<Year = {$(=Only(Year)-1)}>} Aggr((Sum({<Year = {$(=Only(Year)-1)}>} [PZ Poz. Wartość po korekcie])/Count({<Year = {$(=Only(Year)-1)}>} DISTINCT [ZAMD NR])), [Dostawca Skrót], [PZ Nr mag.], [PZ Symbol], [Data], [Indeks Linia], [Indeks Indeks], [Indeks Nazwa], [PZ Poz. Cena po korekcie], [KH_ID])) 
MikeMac84PL
Contributor II
Contributor II
Author

Sunny_talwar 

Thanks for the answer.

I appreciate it.  

I try this ealier but the result is still no data in table. 

Do you have any other ideas?

sunny_talwar

Are you making selection in any other field except for Year field which is directly related to Year field? Like YearStart or MonthYear?

MikeMac84PL
Contributor II
Contributor II
Author

1.png2.png

 

MikeMac84PL
Contributor II
Contributor II
Author

This are all expressions in my table: 

 

Year in polish is ROK

 

year(today())-1 :

=sum({<Year={$(=year(today())-1)}>} [PZ Poz. Wartość po korekcie])

 

year(today()):

=sum({<Year={$(=year(today()))}>}[PZ Poz. Wartość po korekcie])

Vegar
MVP
MVP

Have you tried to double quote your modifier?

=sum({<Year={"$(=year(today())-1)"}>} [PZ Poz. Wartość po korekcie])

MikeMac84PL
Contributor II
Contributor II
Author

=sum({<Rok={$(=Only(Rok)-1)}>}Aggr((sum ({<Rok={$(=Only(Rok)-1)}>}[PZ Poz. Wartość po korekcie])/Count({<Rok={$(=Only(Rok)-1)}>}distinct [ZAMD NR])),[Dostawca Skrót],[PZ Nr mag.],[PZ Symbol],[Data],[Indeks Linia],[Indeks Indeks] ,[Indeks Nazwa],[PZ Poz. Cena po korekcie],KH_ID))/ if(vWaluta=1,vEUR,1)

 

THATS WORKING... 🙂