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]

Look this if is useful for u

[Anonymous]

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

[Miguel_Angel_Baeyens]

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.

[spsrk_84]

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

[spsrk_84]

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

[Anonymous]

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

[Anonymous]

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

[SunilChauhan]

convert the Month year into MMM YYYY

then use as attached file to substract YearMonth

[Miguel_Angel_Baeyens]

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