Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

anindya_manna
Contributor

How to calculate previous month ignoring other selection

I am calculating previous month date(AddMonths(date#(max([Months Number]),'YYYYMM'),-1),'YYYYMM') by using this formula for calculating previous month sales.

But when I am choosing any Rep id then the previous month calculation is going changed as per the rep id selection.

How to keep it fixed? Means I want the previous month is always max(month number)-1 irrespective of any selection.

1 Solution

Accepted Solutions
anindya_manna
Contributor

Re: How to calculate previous month ignoring other selection

=date(AddMonths(date#(max( {1< [FSPM Month Number] = $::[FSPM Month Number]>}  [FSPM Month Number]),'YYYYMM'),-1),'YYYYMM')

8 Replies
jmvilaplanap
Valued Contributor

Re: How to calculate previous month ignoring other selection

Hi

You must to use SetAnalysis, for example:

date(AddMonths(date#(max({1}[Months Number]),'YYYYMM'),-1),'YYYYMM')

agigliotti
Honored Contributor II

Re: How to calculate previous month ignoring other selection

maybe this:

date(AddMonths(date#(max( {1} [Months Number]),'YYYYMM'),-1),'YYYYMM')

anindya_manna
Contributor

Re: How to calculate previous month ignoring other selection

I want to date selection will change the calculation but not rep id or product name field.

if I choose  05/05/2017 then prev moth should be 201704.Date filter should work but not other filter effect the result.

anindya_manna
Contributor

Re: How to calculate previous month ignoring other selection

I want to date selection will change the calculation but not rep id or product name field.

if I choose  05/05/2017 then prev moth should be 201704.Date filter should work but not other filter effect the result.

anindya_manna
Contributor

Re: How to calculate previous month ignoring other selection

I want to date selection will change the calculation but not rep id or product name field.

if I choose  05/05/2017 then prev moth should be 201704.Date filter should work but not other filter effect the result.

jmvilaplanap
Valued Contributor

Re: How to calculate previous month ignoring other selection

Then use the date field inside the set analysis

But can you share an example of the expression you need? (without the set analysis)

agigliotti
Honored Contributor II

Re: How to calculate previous month ignoring other selection

let's try with below:

date(AddMonths(date#(max( {<1 [Months Number] = ::$[Months Number] >} [Months Number]),'YYYYMM'),-1),'YYYYMM')

anindya_manna
Contributor

Re: How to calculate previous month ignoring other selection

=date(AddMonths(date#(max( {1< [FSPM Month Number] = $::[FSPM Month Number]>}  [FSPM Month Number]),'YYYYMM'),-1),'YYYYMM')