topic Re: Only(Year)-1) with Set Analysis (Aggr function) Problem in QlikView

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

MikeMac84PL — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

sunny_talwar — Tue, 10 Mar 2020 14:00:04 GMT

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]))

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

MikeMac84PL — Tue, 10 Mar 2020 14:10:43 GMT

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?

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

sunny_talwar — Tue, 10 Mar 2020 14:12:31 GMT

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

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

MikeMac84PL — Tue, 10 Mar 2020 14:27:49 GMT

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

MikeMac84PL — Tue, 10 Mar 2020 14:28:25 GMT

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])

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

Vegar — Tue, 10 Mar 2020 15:15:35 GMT

Have you tried to double quote your modifier?

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

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

MikeMac84PL — Wed, 11 Mar 2020 07:53:09 GMT

=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... 🙂