How to show last 6 month with Month-Year as a dimension

[kavita25]

Hi,

I want to show last 6 month of Order Date with month-year as a dimension of Sale Date.

I am able to achieve last 6 month of order date on the basis of selection of Month Year(Sale Date).

For e.g If i select Jun-2015, it shows Last 6 Months of Order Date i.e Jun Jul Aug Sep Oct Nov Dec.

Please help me with this issue.

Its Urgent.

Regards,

Kavita

[#2]

Hi Kavita,

Can you please try using below code and see are expecting the same result.

Script Code:

input:

LOAD PSales_postedDate,

     TravelDate,

     sss,

     Year(PSales_postedDate)&if(len(num(Month(PSales_postedDate)))=1,'0'&num(Month(PSales_postedDate)),num(Month(PSales_postedDate))) as PSalesconv,

     Year(TravelDate)&if(len(num(Month(TravelDate)))=1,'0'&num(Month(TravelDate)),num(Month(TravelDate))) as TravelDateConv

FROM

(biff, embedded labels, table is Sheet1$);

In List Box, Select "PSalesconv" Field

Take a Straight table, Take the dimension is TravelDateConv and the expression

"sum({$<TravelDateConv={'>=$(=max(PSalesconv)) <=$(=Max(PSalesconv)+6)'}>} sss)"

By using this I got the below result.

Thanks,
Sreeman

[kavita25]

@sunindia

Please help me with this issue. I am not able to find the solution yet.

[#4]

Hi kavita,

Did you see my solution? Did it work?

Thanks,
Sreeman

[sunny_talwar]

Try to understand what this means Kavita:

Sales date is for Jun-2015 and Next 3 months should be for Jun-2015 to Dec 2015.(Travel Date)

Can you elaborate a little?

[kavita25]

In the data excel sheet, Sale Date is for Jun 2015, but the result should be Next 6months of Travel Date.

It means the Dimension will be Jun 2015, but the value should contain for Next 6 months.

[sunny_talwar]

May be this:

=Count(If(TravelDate >= MonthYear and TravelDate <= AddMonths(MonthYear, 6), sss))

[sunny_talwar · Accepted solution]

Or May be this without the equal sign in the second condition within if:

=Count(If(TravelDate >= MonthYear and TravelDate < AddMonths(MonthYear, 6), sss))

[kavita25]

Thank You Once Again....

[sunny_talwar]

Just as an alternative, if you want to explore and because it is more efficient, you can create flag in the script and use set analysis:

Table:

LOAD *,

  If(MonthYear_TravelDate >= MonthYear and MonthYear_TravelDate < AddMonths(MonthYear, 6), 1, 0) as Flag;

LOAD PSales_postedDate,

  MonthName(PSales_postedDate) as MonthYear,

    TravelDate,

    MonthName(TravelDate) as MonthYear_TravelDate,

    sss

FROM

[dATA (1).xls]

(biff, embedded labels, table is Sheet1$);

Your expression would now be;

=Count({<Flag = {1}>} sss)