YearMonth subtraction

[Anonymous]

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!

[Anonymous]

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.

[Miguel_Angel_Baeyens]

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

[Anonymous]

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.

[Anonymous]

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(?).

[SunilChauhan]

are you asking about

monthstart(addmonth(date,-1))

or

monthstart(date)

[Miguel_Angel_Baeyens]

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

[Anonymous]

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.

[Miguel_Angel_Baeyens]

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