Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
anindya_manna
Partner - Creator II
Partner - Creator II

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
Partner - Creator II
Partner - Creator II
Author

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

View solution in original post

8 Replies
jmvilaplanap
Specialist
Specialist

Hi

You must to use SetAnalysis, for example:

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

agigliotti
Partner - Champion
Partner - Champion

maybe this:

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

anindya_manna
Partner - Creator II
Partner - Creator II
Author

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
Partner - Creator II
Partner - Creator II
Author

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
Partner - Creator II
Partner - Creator II
Author

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
Specialist
Specialist

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
Partner - Champion
Partner - Champion

let's try with below:

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

anindya_manna
Partner - Creator II
Partner - Creator II
Author

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