topic Re: YearMonth subtraction in QlikView

YearMonth subtraction

Anonymous — Fri, 14 Oct 2011 10:20:26 GMT

Hi,

I found a problem with my max(YearMonth) - x expression.

When it comes to overlaping Years. Say that max(YearMonth) = 201110 and mu expression is max(YearMonth)-11 i get 201190 when i wanted it to be 201011.

Do i need to rank YearMonth instead in the script before doing this expression or any better ideas?

Thanks!

Re: YearMonth subtraction

Anonymous — Fri, 14 Oct 2011 10:45:52 GMT

Look this if is useful for u

YearMonth subtraction

Anonymous — Fri, 14 Oct 2011 11:52:21 GMT

For some reason i can't download files from here. Probably firewall.

Re: YearMonth subtraction

Miguel_Angel_Baeyens — Fri, 14 Oct 2011 12:00:28 GMT

Hi,

That YearMonth is not a date in itself. You can do that using Date(Field, 'YYYYMM') so there is an underlying numeric value that will allow you to add or substract. But I find easier to use always in your charts the main Date field in the expressions and a range from-to, something like

Sum({< DateField = {'>=MonthStart(AddYears(Today(), -1))<=$(=MonthEnd(Today()))'} >} Sales)

If your dimension is MonthYear you will get as you expect instead of doing some tricky sorting by expression and so.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

EDIT: I had an example on this. Check both charts in the attached example and see how YearMonth is sorted right nby date, and DateField is the one used in the expressions. All right, you cannot download QVW files, then check the following script:

Table:
LOAD Chr(64 + Ceil(Rand() * 3)) AS ID,
           Date('01/01/2009' + Ceil(Rand() * 1300)) AS DateField,
           Ceil(Rand() * 1000) AS Amount
AUTOGENERATE 20;
Calendar:
LOAD DateField,
           Text(Date(DateField, 'YYYYMM')) AS YearMonth
RESIDENT Table;

Now create a new bar chart, set YearMonth as dimension and the following as expression:

Sum({< DateField = {'>=$(=MonthStart(AddYears(Today(), -1)))<=$(=MonthEnd(AddMonths(Today(), -3)))'} >} Amount)

Go to the Sort tab, click on YearMonth, check "Expression" and type just DateField.

Re: YearMonth subtraction

spsrk_84 — Fri, 14 Oct 2011 12:31:37 GMT

Hi,

one simple suggestion instead of representing yearmonth like this 201110 try like this 2011010(YearMon)

such that when u substract also 2011010-1 =2011009

Regards,

Shiva

Re: YearMonth subtraction

spsrk_84 — Fri, 14 Oct 2011 12:32:04 GMT

Hi,

one simple suggestion instead of representing yearmonth like this 201110 try like this 2011010(YearMon)

such that when u substract also 2011010-1 =2011009

Regards,

Shiva

YearMonth subtraction

Anonymous — Mon, 17 Oct 2011 06:27:18 GMT

But in my chart i can't use year month as a dimension.

YearMonth subtraction

Anonymous — Mon, 17 Oct 2011 06:28:10 GMT

This will still give me a problem with subtraction over years. 201100101 - 1 = 201100100

Re: YearMonth subtraction

SunilChauhan — Mon, 17 Oct 2011 07:08:11 GMT

convert the Month year into MMM YYYY

then use as attached file to substract YearMonth

Re: YearMonth subtraction

Miguel_Angel_Baeyens — Mon, 17 Oct 2011 07:16:46 GMT

Hi,

Why not? Do you have continuous axes? What fields do you have available to show in the chart?

Regards.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

YearMonth subtraction

Anonymous — Mon, 17 Oct 2011 07:24:38 GMT

I mean that in my chart it's not ment to show year month. i have a straight table where i look at departments and how well they're doing certain periods of time. past 3 months, past 6 months, past 12 months. aggregated. So this is why i "can't" show yearmonth as a dimension.

Re: YearMonth subtraction

Miguel_Angel_Baeyens — Mon, 17 Oct 2011 07:33:49 GMT

All right,

I think I got it now. Then you only need to specify a range of dates in your set analysis. Say you want to show sales for the last 3 months (starting today, 3 months backwards or 17/08/2011 - 17/10/2011), your expression should be

Sum({< DateField = {">=$(=AddMonts(Today(), -3))<=$(=Date(Today()))"} >} Sales)

regardless your dimensions. You can use MonthField as dimension to show Oct, Sep and Aug, in the case above.

Note that the format returned by the functions AddMonths() and Date() must be the same that DateField.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

YearMonth subtraction

Anonymous — Mon, 17 Oct 2011 12:30:28 GMT

Problem is, we want to see whole months. So if today is 2011-10-17 the three months vill be

2011-07-01 to 2011-09-30

//A.

YearMonth subtraction

Anonymous — Mon, 17 Oct 2011 12:36:46 GMT

is there a way to get the beginning of the month with addmonths function? I mean the end is doable but i can't find the start thing(?).

Re: YearMonth subtraction

SunilChauhan — Mon, 17 Oct 2011 12:41:49 GMT

are you asking about

monthstart(addmonth(date,-1))

monthstart(date)

Re: YearMonth subtraction

Miguel_Angel_Baeyens — Mon, 17 Oct 2011 15:44:30 GMT

Hi,

What I wrote above was just an example. There are dozens of functions you can use, but in your particular case (three complete months from 01/07/2011 to 30/09/2011) the correct syntax should be:

Sum({< DateField = {">=$(=AddMonths(MonthStart(Today()), -4))<=$(=MonthEnd(AddMonths(Today(), -1)))"} >} Sales)

If this kind of time analysis will take place usually in your chart, I recommend you to create a master calendar linked to your main fact table, where you set a flag field to know quickly the last three months, last quarter, last month, last year and so. Check this application on how to create it and many other posts in the QlikCommunity.

Hope that helps and gives you an idea on how to move ahead.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

YearMonth subtraction

Anonymous — Tue, 18 Oct 2011 10:32:38 GMT

Thank you! Very helpful! Canät download the aplication right now though but is there a same way to du with weeks or do i have to work with dates?

//A.

YearMonth subtraction

Miguel_Angel_Baeyens — Tue, 18 Oct 2011 10:36:26 GMT

Hi,

I alway do likewise, using the DateField (or whatever is named in your application) in the left part of the equal in the set analysis, and using date functions to get the proper range of dates in the right part of the equal sign. This is the best way to get the results as you expect without playing with string functions that usually perform poorer than the numeric ones (dates are numeric in QlikView).

If you cannot download the file I can send it to you by email so you can check botht he script and the charts where this kind of expressions are used, let me know anyway by PM.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica