topic (year month)-1 Calculation in QlikView

(year month)-1 Calculation

Anonymous — Thu, 18 Dec 2014 18:42:23 GMT

hi gurus,

How to calculate YearMonth-1

1-(Unresolved Tickets in Last Month)/currennt Month Solved

I have this type Calculation

Thanks

Re: (year month)-1 Calculation

MarcoWedel — Thu, 18 Dec 2014 19:01:38 GMT

Can you provide sample data?

Re: (year month)-1 Calculation

Josh_Good — Thu, 18 Dec 2014 19:17:33 GMT

Are,

This will depend on exactly how you are creating your YearMonth field. I recommend doing it using the MonthStart function so the field remains a date and you can use date math to get other dates (e.g Pervious YearMonth). So your load script will look something like this:

Load

MyDate,

Date(MonthStart(MyDate), 'YYYY-MMM') as YearMonth,

Date(MonthStart(AddMonths(MyDate,-1)), 'YYYY-MMM') as PreviousYearMonth,

....

I hope this helps. If it doesn't please post some sample data as suggested by MarcoWedel

-Josh

Qlik

Re: (year month)-1 Calculation

Anonymous — Thu, 18 Dec 2014 19:33:28 GMT

hi thanks ,

date:9/11/2013

yearmonth=2013-9

Year Month: 2013-9 2013-10 2013-11 2013-12

unresolved 2 2 2 99

resolved 67 78 90 120

calculation: 1-un resolved in last month/solved in current month

problem: unresolved in last month should change dynamically to selected Month

if i select November Month,it should take october unresolved/november solved in the chart expression.dimension is yearMonth

thanks

Re: (year month)-1 Calculation

Josh_Good — Thu, 18 Dec 2014 19:51:08 GMT

Hi Are,

Here is what I came up with. I loaded the data like this:

Load

Date(MakeDate(Year, Month, 1), 'YYYY-M') as YearMonth,

Date(AddMonths(MakeDate(Year, Month, 1),-1), 'YYYY-M') as PreviousYearMonth,

Unresolved,

Resolved;

Load * Inline

[

Year, Month, Unresolved, Resolved

2013, 9, 2, 67

2013, 10, 3,78

2013, 11, 4, 90

2013, 12, 99, 120

];

Then I used this expression to get the previous month's unresolved:

=Sum({$<YearMonth = {$(=Chr(39) & Max(PreviousYearMonth) & Chr(39))}>} Unresolved)

I've also attached the QVW for your reference.

-Josh

Qlik

Re: (year month)-1 Calculation

Anonymous — Sun, 21 Dec 2014 07:10:22 GMT

Hi Josh Thanks for your time,

Please find the attached sample application

thanks,

Re: (year month)-1 Calculation

Josh_Good — Mon, 22 Dec 2014 16:51:14 GMT

Hi Are,

Here is the solution I created above put into your data. Note, that your Created_Period was not actually a date but a text string, in my load script changed it to be a date.

-Josh