topic Re: How to get previous Month data in QlikView

How to get previous Month data

Mon, 16 Nov 2015 10:45:56 GMT

Hi All,

in my report I have calendar objects for FromDate and ToDate. when I select a date range it is giving the results.

here my issue is if I select a date range in a particular month, will I get both selected month and previous month data in the same report(Line Chart).

Example: when I select Date range 01-October-2015 to 31-Oct-2015. it will give the total volume for the October Month, I want to get Previous month September total volume.

Dimension: MonthYear(Jan-2015)

Expression: Sum(Amount)

Please help me,

Thanks.

Re: How to get previous Month data

Chanty4u — Mon, 16 Nov 2015 10:50:25 GMT

hi,

Current-mnth

=sum({<Date={'$(=Date(Max(Date),'M/D/YYYY'))'}>}Balance)

Previous:mnth

=sum({<Date={"$(=Date(Max({1}{<Date={'<$(=Date(Max(Date),'M/D/YYYY'))'}>}Date),'M/D/YYYY'))"}>}Balance)

hope this helps u

thanks

Suresh

Re: How to get previous Month data

sunny_talwar — Mon, 16 Nov 2015 11:09:51 GMT

May be this for previous month:

=If(Sum(Amount) > 0, Above(Sum({<Date, MonthYear>}Amount)))

Re: How to get previous Month data

PrashantSangle — Mon, 16 Nov 2015 11:15:29 GMT

Hi,

try

sum({<Date={">=Date(addmonths(Monthstart(max(Date),-1)))<=Date(addmonths(max(Date),-1))"}>}Amount)

Regards

Re: How to get previous Month data

Kushal_Chawda — Mon, 16 Nov 2015 11:23:25 GMT

For previous month

=sum({<Date={">=$(=Date(monthstart(addmonths(max(Date),-1))))<=$(=Date(monthend(addmonths(max(Date),-1))))"}, Year=,Month=, Date=>}Amount)

Re: How to get previous Month data

Anonymous — Mon, 16 Nov 2015 11:41:47 GMT

Use MonthStart() and MonthEnd() as suggested by Max/Kush

Re: How to get previous Month data

sunny_talwar — Mon, 16 Nov 2015 11:51:45 GMT

Is this what you want?

Use this dummy script:

Table:

LOAD Date(MakeDate(2015,1,1) + RecNo() - 1) as Date,

MonthName(MakeDate(2015,1,1) + RecNo() - 1) as MonthYear,

Ceil(Rand() * 100000) as Value

AutoGenerate (Today() + 1 - MakeDate(2015,1,1));

Re: How to get previous Month data

effinty2112 — Mon, 16 Nov 2015 12:20:16 GMT

Hi,

If you have a dimension called MonthYear then I'm guessing you've got a master calendar. If you add a MonthID field to the calendar you'll make life easy. If your Calendar is loaded in the usual way, creating dates from the earliest to the latest, then if you add something like this above your master calendar script:

LOAD

AutoNumber(MonthYear , 'MonthID') as MonthID

;

LOAD ...

You'll have a nice integer field you can do simple arithmetic on. Now if you select a month an expression with the set modifier <MonthID = {$(=Max(MonthID)-1)}, MonthYear =>} will give the calculation for the previous month. You may need to add other terms to this, such as ... Month =, Year =... so that it will disregard other selections as may be required. Then to tidy it all up you can create a variable to hold your set modifier and if you want add a parameter for the number of months you want to look back instead of having 1 hard written then you have a very flexible and easy to read and maintain solution

Cheers

Andrew