Doing Sum in text box with set analysis

[alec1982]

Hi all,

I am trying to get a text box to show count of distinct Sales Orders where the bill date is within a period of time.

Sales Order     Bill Date                    Period

1                       03-03-2014              201403

2                       01-03-2014              201403

3                       04-05-2012              201403

4                       01-01-2011              201312

I need a text box to show me the count of the distinct  sales Order by Period where the bill date is within this period. so if the period is 201403 then I need the count where the bill date is within March 2014.

Your help is appreciated..

Thxs

[its_anandrjs]

Write like below in the text box

=Count(DISTINCT {<Period = {'201403' }> } [Sales Order] )

Do you have month field also if not then add month field like 

Month([Bill Date])  as Month then use below script

=Count(DISTINCT {< Period = {'201403'}, Month = {'Mar'}  >} [Sales Order] )

[Anonymous]

See attached. Is this what you need?

[#4 · Accepted solution]

Badr,

has your period always 'YYYYMM' format?

if yes, you need something like this ....

Let me know if it helps.

And take care about date formats

regards

Darek

[alec1982]

Hi,

This wrong as it will include Sales Order Nr3 which is not in the right period.

Thxs

[its_anandrjs]

Load your table like below script and introduce Month field on the table

T:

LOAD *, Date#([Bill Date],'DD-MM-YYYY') AS [Billing Date];

LOAD *, Month(Date#([Bill Date],'DD-MM-YYYY')) as Month;  // This field is from the Bill Date.

LOAD * INLINE

[

    Sales Order,Bill Date, Period

    1,          03-03-2014,201403

    2,          01-03-2014,201403

    3,          04-05-2012,201403

    4,          01-01-2011,201312

];

NoConcatenate

New:

LOAD

[Bill Date],

[Sales Order],

Period,

Month

Resident T;

DROP Table T;

Other wise you can add the Flag field on this also

And on the Text object write

=Count(DISTINCT {< Period = {'201403'}, Month = {'Mar'}  >} [Sales Order])

And it gives output like

Regards

[alec1982]

Thank you!