How to make an measure without max(year)

[Louveduval]

Hello,

I'm trying to create a pivot table with my Sales, Sales Y-1, and Evolution. My dimensions are Product and Year, and I want to achieve this without modifying the script (otherwise, where's the fun in that?).

For Sales, I simply use SUM(Sales), which is correctly filtered by Product and Year—no problem there.
However, when I try to calculate Sales for Y-1, it's not the same level. I've tried using P(), MAX(total <Year> Year), and Only(), but nothing seems to work as expected.

Do you know how I can achieve this? :3

Thanks! 😊

[brunobertels]

Hi 

May be try this :

Current Year Sales

=Sum({<Year = {'$(=Max(Year))'}>} Sales)

Previous Year Sales

=Sum({<Year = {'$(=Max(Year)-1)'}>} Sales)

Regards

[marcus_sommer]

Such things are possible with interrecord-functions like above/below/before/after which enable in general the access to each visible cell of the chart. But by complex views it's not mandatory trivial to set their second/third parameter and/or to embed them within (nested) range- and aggr-functions. Therefore keeping it as simple as possible could save many efforts.

Above - chart function | Qlik Cloud Help

Easier may be to remove the "restricting" dimension - here the Year - and to apply n expressions with a n appropriate reference to the wanted year like shown by @brunobertels.

[Louveduval]

I don't want to use Max(Year) because that use the max or the min of my script and not the max or min from my column  

(pour les fan de rugby : le problème est que je veux avoir l'information grâce à la  colonne année du tableau croisée et pas avec l'année du script et  devoir faire beaucoup de mesures avec -2 pour comparer 2023/2024, -3 pour 2022/2023  etc ... par exemple)

[brunobertels · Accepted solution]

Bonjour 

Alors dans ce cas essaye avec before : 

année N 

Sum(Sales) 

la colonne d'avant (N-1) 

before(sum(sales)) 

et before accepte un offset dans ces paramètres : 

before(sum(sales),2)

[Louveduval]

je vais essayer ça demain 🙂 

[Chanty4u]

Try this 

Sum({<Year = {"$(=Only(Year)-1)"}>} Sales)

[Louveduval]

'only' work if I use an active selection  🙂 

[Louveduval]

this solution is easy and work for me , thank you very much !!! 

[brunobertels]

Bonjour 

Bonne nouvelle, ravi d'avoir pu aider 🙂 

Bonne journée.