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!

18 Replies
paul_hallwood
Contributor II
Contributor II
Author

Hello Nirmal,

This would seem to work fine if we had date fields available to us.  But I'm just wondering, for some of our reports the information is only held for Month and Year, of which there are approximately 2 years worth of data.

Is there any way you could manipulate your formula so that it caters for this.  i've tried myself but with no success. 😞

jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you please attach some sample data and sample file?

Regards,

Jagan.

Not applicable

Hi Paul

   Can you please attach your sample application

paul_hallwood
Contributor II
Contributor II
Author


Here's a sample of what I'm trying to do (stripped down and with no sensitive data).

As you can see the current month selected seems to work fine.  When I try to retrieve the previous months data (-1 from the month) nothing seems to come through.

Cast your eyes over it and it'll be much appreciated if you can offer any help.

Thanks everyone for their speedy answers!

paul_hallwood
Contributor II
Contributor II
Author

Hi Manish,

The weird thing is, getting the current months activty works, but when I try and get the previous month's activity (by adding -1 to the formula) I don't get any data coming through.

I've attached my example file in reply to someone else's question above if you would like to have a look at this.

jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this expression

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

Regards,

Jagan.

paul_hallwood
Contributor II
Contributor II
Author

I've just attched it in reply to someone else asking for it.

I hope this helps! 🙂

paul_hallwood
Contributor II
Contributor II
Author


Whoo-hoo!  This seems to have done the trick!

Thanks you everyone for you time spent one this.  It's very much appreciated.


Kind regards,

Paul

MK_QSL
MVP
MVP

Jagan, nice solution... but what if user select 2013 January as current month?