How to get next 12 months data?

[#1]

Hi,

I have a date field(year-month) and I want to get count of contracts for next 12 months on selecting any date.

How to do it?

Thanks

Saurabh

[#2]

Hi, But if we use 1 in the expression then it will show count for all the values, not for the selected values.

[jagan]

Hi,

Try like this

=count({<[orderDate]={">=$(=Date(monthstart(max([orderDate]), 1), 'M/D/YYYY hh:mm:ss TT'))

<=$(=Date(monthend(max([orderDate]), 12), 'M/D/YYYY hh:mm:ss TT'))"}>}quantityOrdered)

Regards,

Jagan.

[jagan · Accepted solution]

Hi,

I think it is better to truncate the Time part in the date field since you are not using this, you can do this like below

LOAD comments,

    customerNumber,

    Date(orderDate) AS orderDate,

    orderNumber,

    requiredDate,

    shippedDate,

    status;

SQL SELECT comments,

    customerNumber,

    orderDate,

    orderNumber,

    requiredDate,

    shippedDate,

    status

FROM Orders;

=count({<[orderDate]={">=$(=monthstart(max([orderDate]), 1))

<=$(=monthend(max([orderDate]), 12))"}>}quantityOrdered)

Hope this helps you.

Regards,

Jagan.

[its_anandrjs]

Try this way also but in your data model the date in time stamp so in place of the use Date only for that in the load script add script for only date

LOAD comments,

    customerNumber,

    Date( [orderDate],'MM/DD/YYYY') AS [orderDate]

    orderNumber,

    requiredDate,

    shippedDate,

    status;

SQL SELECT comments,

    customerNumber,

    orderDate,

    orderNumber,

    requiredDate,

    shippedDate,

    status

FROM Orders;

And then in charts use below expression

Sum({< [orderDate] = {'>=$(=AddMonths(Max(Date),-12))  <=$(=Max(Date))'}>} quantityOrdered)

Because in the SET expression it create problems.

Regards

Anand

[#6]

Hi Anand,

I am not getting the result as per your expression. It restricts me from January to selected order date.

But I have got the answer.

May be you would like to see-

=sum({<[orderDate]={">=$(=monthstart(max([orderDate]), 1))<=$(=monthend(max([orderDate]), 12))"}>}quantityOrdered)

Thanks

Saurabh