topic Re: Comparaison Year/(Year -1) using selection in QlikView

Comparaison Year/(Year -1) using selection

reyouk3310 — Sat, 16 Nov 2024 04:26:57 GMT

Hi all, (Frensh version below)

I have a comparison to make according to the active selection. I have a histogram, from which I will choose one or more months and I have a table above in which I wish to compare the current year and the previous year only for the months selected in the histogram.

As in the example below: I selected the months 01/2019 and 02/2019

=> Column Y in the table shows me the cumulative of these two months for the current year and this is my first goal

=> The column Y-1 of the table should show me the cumulative of the same months for the previous year (01/2018 and 02/2018) but this is not the case.

The expression I used for Y-1 is:

Count({$<Year={"$(=max({<Source_Cash={'Cash'}>}Year)-1)", [Year-Month]={"$(=GetFieldSelections([Year-Month]))"}>}[Value])

Knowing that Year-Month is the dimension used in the histogram.

Any idea please ?

Thanks a lot

--------------------------------------------------------------------------------------------------------------------

(Frensh Version)

Bonjour tout le monde,

J'ai une comparaison à faire selon la sélection active. J'ai un histogramme, à partir duquel je vais choisir un ou plusieurs mois et j'ai un tableau au dessus dans lequel je souhaite faire la comparaison entre l'année en cours et l'année précédente seulement pour les mois sélectionnés dans l’histogramme.

Comme dans l'exemple ci dessous: J'ai sélectionné les mois 01/2019 et 02/2019

=> La colonne Y du tableau m'affiche le cumule de ces deux mois pour l'année en cours et c'est mon premier objectif

=> La colonne Y-1 du tableau devrai m'afficher le cumule des mêmes mois pour l'année précédente (soit 01/2018 et 02/2018) mais ce n'est pas le cas.

l'expression que j'ai utilisé pour Y-1 est la suivante:

Count({$<Year={"$(=max({<Source_Cash={'Cash'}>}Year)-1)", [Year-Month]={"$(=GetFieldSelections([Year-Month]))"}>}[Value])

Sachant que Year-Month est la dimension utilisé dans l'histogramme.

Quelqu’un a une idée ?

Merci pour votre retour

Re: Comparaison Year/(Year -1) using selection

sunny_talwar — Thu, 14 Feb 2019 15:07:12 GMT

What expression did you use for Y?

Re: Comparaison Year/(Year -1) using selection

pradosh_thakur — Thu, 14 Feb 2019 15:11:56 GMT

The as-of-table might be the answer you are looking for. can you tell us more about the table fields.

https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

Re: Comparaison Year/(Year -1) using selection

reyouk3310 — Thu, 14 Feb 2019 15:41:05 GMT

For Y

I use a simple expression : Count([Value])

=> So if no value is selected in my barshart, Y shows me all data , but if I select one bar or more from my barshart, it will accumulate data the selected bar.

For Y-1

I have used the following expression :

Count({$<Year={"$(=max({<Source_Cash={'Cash'}>}Year)-1)", [Year-Month]={"$(=GetFieldSelections([Year-Month]))"}>}[Value])

=> Year={"$(=max({<Source_Cash={'Cash_Services'}>}Year)-1)"} : is used to precise that I need the previous Year of the max year selected

=> [Year-Month]={"$(=GetFieldSelections([Year-Month]))"}; is used to precise that I need the accumulate of selected months

Thank you

Re: Comparaison Year/(Year -1) using selection

sunny_talwar — Thu, 14 Feb 2019 15:54:17 GMT

Do you have a Month field which doesn't have Year attached to it? If you do, then try this

Count({$<Year={"$(=max({<Source_Cash={'Cash'}>}Year)-1)"}, [Year-Month], Month = p(Month)>}[Value])