Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

[QlikSense]Calculation following filter?

Hello,

I want to compare the revenue with that of the previous year following the month and the year that we filter.

If the filter is not applied, the month and the year is set following current date (below is the formula that we generated)

((sum({<month_n = {"<=$(vM)"}, year = {"$(vY)"}>}[revenue])

- sum({<month_n = {"<=$(vM)"}, year = {"$(vY)"}>}[salary_euro])

)

/

(sum({<month_n = {"<=$(vM)"}, year = {"$(vY1)"}>} [revenue])

-sum({<month_n = {"<=$(vM)"}, year = {"$(vY1)"}>} [salary_euro])

))

- 1

Knowing that vM, vY and vY1 are defined in the script already. They are set to collect the current month, the current year and the previous year.

Else the revenue will be sum up according to the month and the year chosen ( month and year can be multiple selection). -> I'm stuck here.

How could I integrate the ELSE part in the formula above and run it in qliksense?

THANK YOU FOR YOUR SUPPORT A LOT!

Tags (2)
2 Replies
brunobertels
Valued Contributor

Re: [QlikSense]Calculation following filter?

Hi

Are your variable something like that ? :

vM = month(today())

vY = year(today())

VY1 = year(today()-1)

If it's that, may be when you select specific month in your filter pan your mesure still send back curent month rather than selected month ?

may be try like this :

vM=max(month_n)

vY=max(year)

vY1=max(year)-1

so that without selection your mesure and set analysis will take as month the last month of the max year in your data

if you select one or several month , your mesure will take as month the max month of your selected month and same with vY1 that will send back the previous year of the selected year or with no selection the previous year of your max year presents in your database.

Hope it's help

Bruno

Not applicable

Re: [QlikSense]Calculation following filter?

Thank you for your tips!! It works!

Community Browser