topic Re: Previous Months Data in QlikView

Previous Months Data

paul_hallwood — Fri, 06 Dec 2013 12:05:18 GMT

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!

Re: Previous Months Data

Fri, 06 Dec 2013 12:11:26 GMT

try this

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

Variable:

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

Re: Previous Months Data

MK_QSL — Fri, 06 Dec 2013 12:19:07 GMT

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

Re: Previous Months Data

Anonymous — Fri, 06 Dec 2013 12:21:25 GMT

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

Re: Previous Months Data

paul_hallwood — Fri, 06 Dec 2013 12:43:09 GMT

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

Re: Previous Months Data

MK_QSL — Fri, 06 Dec 2013 13:05:17 GMT

Try This

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

Re: Previous Months Data

paul_hallwood — Fri, 06 Dec 2013 13:39:58 GMT

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

Re: Previous Months Data

MK_QSL — Fri, 06 Dec 2013 13:50:33 GMT

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

Re: Previous Months Data

paul_hallwood — Fri, 06 Dec 2013 13:56:22 GMT

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

Re: Previous Months Data

MK_QSL — Fri, 06 Dec 2013 14:18:30 GMT

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

Re: Previous Months Data

paul_hallwood — Fri, 06 Dec 2013 14:20:39 GMT

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

Re: Previous Months Data

jagan — Fri, 06 Dec 2013 14:41:52 GMT

Hi,

Can you please attach some sample data and sample file?

Regards,

Jagan.

Re: Previous Months Data

Fri, 06 Dec 2013 15:15:40 GMT

Hi Paul

Can you please attach your sample application

Re: Re: Previous Months Data

paul_hallwood — Fri, 06 Dec 2013 15:20:24 GMT

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!

Re: Previous Months Data

paul_hallwood — Fri, 06 Dec 2013 15:23:08 GMT

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.

Re: Previous Months Data

jagan — Fri, 06 Dec 2013 15:30:31 GMT

Hi,

Try this expression

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

Regards,

Jagan.

Re: Previous Months Data

paul_hallwood — Fri, 06 Dec 2013 15:33:47 GMT

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

I hope this helps! 🙂

Re: Previous Months Data

paul_hallwood — Fri, 06 Dec 2013 15:35:42 GMT

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

Re: Previous Months Data

MK_QSL — Fri, 06 Dec 2013 15:51:23 GMT

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