Previous Month

[sergio]

Hi there, i have a problem with set analysis again. Here i go:

I have a table with inbounds calls with the following structure:

F_INBOUND_DATES:

LOAD

F_Primary_ID,

F_Date,

F_DatePreviousMonth,

F_Year,

F_Month,

F_PrevMonth,

F_MonthYear,

F_PrevMonthYear

FROM

\$(_SysDirQvd) \LK_TIE_DIA.qvd (qvd);

And a table of facts like this:

BT_INBOUND:

LOAD

F_Primary_ID,

BT_QInbound,

BT_QLostInbound,

BT_Channel,

....etc

FROM \$(_SysDirQvd) \BT_IN.qvd (qvd);

I need to do a pivot table with a comparison between actual month and previous month of the BT_QInbound. For that, I have created the following table:

The expression on previous month like this:

if(F_Month=1,

sum({$<F_Month={"$(=only(F_PrevMonth))"},F_Year={"$(=only(F_Year-1))"}>} BT_QInbound),

sum({$<F_Month={"$(=only(F_PrevMonth))"},F_Year={"$(=only(F_Year))"}>} BT_QInbound)

)

Until here evrithing it's ok, but when i select a range of days of a month the column Q Previous Month is set all to null (-), and i need that column shows the same sum but only during the days i select.

Well, thanks to all for the information.

Regards!

[sergio · Accepted solution]

I have found a solution, the correct expression is:

if(F_Month=1,

sum({$<F_Month={"$(=only(F_PrevMonth))"},F_Year={"$(=only(F_Year-1)),F_Date={"*"}"}>} BT_QInbound),

sum({$<F_Month={"$(=only(F_PrevMonth))"},F_Year={"$(=only(F_Year)),F_Date={"*"}"}>} BT_QInbound)

)

[#3]

HI Sergio

in you expression you have hard coded month to 1

F_Month = 1

how will it be dynamic ????

[sergio]

AshwinPagare wrote:
HI Sergio
in you expression you have hard coded month to 1
F_Month = 1
how will it be dynamic ????<div></div>

Hi AshwinPagare, i'm not hard coding, i'm asking for January to know if i have to substract 1 to the year or not, because the previous year to january 2011 is december 2010.

Regards!