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: 
paul_hallwood
Contributor II
Contributor II

Previous Months Data

Hello All,

I hope someone can help with my issue I'm currently experiencing.

Basically, what I want to happen is, when someone selects a month, the previous 11 months of activity are brought into 11 text boxes (one for each previous month).

I'm curently testing this out with just one month and I'm getting no joy.  This is the formula I am using within the textbox that represents the first previous month:

 

=Sum({$<[Year]={$(=Only([Year]))}, [Month] = {$(=Only([Month])-1)} >} Activity)

I've searched high and low on this forum but the solutions I have tried have not brought me any joy.  Any help is much appreciated.

Thanks in advance!

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this expression

=Sum({$<[Year]={$(=Only([Year]))}, SMonthName=, [Month] = {$(=Only([Month])-1)} >} Activity)

Regards,

Jagan.

View solution in original post

18 Replies
Not applicable

try this

=Sum({$< Datefield = {$(Vprevmonth)} >} Activity)


Variable:

Vprevmonth=Month(addmonths(max(Datefield),-1))

MK_QSL
MVP
MVP

=SUM({$<Year = {'$(=Year(Today()))'}, Month = {'$(=Month(Today())-1)'}>}Activity)

Anonymous
Not applicable

Yes What Nirmal is said is correct now copy paste the expression for Frist text box which show previos month and now for each month text box do   Sum({$< Datefield = {$(Vprevmonth-1)} >} Activity) for second text box and again Sum({$< Datefield = {$(Vprevmonth-2)} >} Activity) and so on so now when you select one year and particular month each text box shows previous month activity


Regards,

anant

paul_hallwood
Contributor II
Contributor II
Author


I can't seem to get this formula to work Manish.  Any idea why???

MK_QSL
MVP
MVP

Try This

=SUM({$<Year = {'$(=Year(Today()))'}, Month =  {'$(=Date(AddMonths(Today(),-1),'MMM'))'>}Activity)

paul_hallwood
Contributor II
Contributor II
Author

Hmm, it's highlighting the MMM part of the formula as if it's in error.  And when I put that into the textbox it returns "-".

MK_QSL
MVP
MVP

=SUM({<Year = {'$(=Year(Today()))'}, Month = {"$(=Date(AddMonths(Today(),-1),'MMM'))"}>}Activity)

paul_hallwood
Contributor II
Contributor II
Author

Still not working unfortunately.  It turns everything inbetween the quote marks a redish-purple colour, as if it's a field.

MK_QSL
MVP
MVP

if your Month is in Number format below should work

=SUM({$<Year = {'$(=Year(Today()))'}, Month = {'$(=Month(Today())-1)'}>}Activity)


if your Month is in Text Format below should work

=SUM({<Year = {'$(=Year(Today()))'}, Month = {"$(=Date(AddMonths(Today(),-1),'MMM'))"}>}Activity)


Considering that Month is 'MMM' format..

If none of above is working, let us have your sample file. There should be some Formatting issues..