Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

Re: Previous Months Data

Hi,

Try this expression

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

Regards,

Jagan.

View solution in original post

18 Replies
Highlighted
Not applicable

Re: Previous Months Data

try this

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


Variable:

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

Highlighted
MVP
MVP

Re: Previous Months Data

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

Highlighted
Specialist
Specialist

Re: Previous Months Data

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

Highlighted
Contributor II
Contributor II

Re: Previous Months Data


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

Highlighted
MVP
MVP

Re: Previous Months Data

Try This

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

Highlighted
Contributor II
Contributor II

Re: Previous Months Data

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 "-".

Highlighted
MVP
MVP

Re: Previous Months Data

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

Highlighted
Contributor II
Contributor II

Re: Previous Months Data

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

Highlighted
MVP
MVP

Re: Previous Months Data

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