How to restrict data up to current month in present year.

[ashokpaladugula]

Hi all,

Customer	PickupDate	Order Number	Mode
Nexteer	2015-01-01 18:15:00.000	2784436	Truckload
Regal	2015-01-02 06:15:00.000	2744436	LTL
Robert	2016-07-12 18:00:00.000	2254436	Truckload
MASCO	2016-07-18 12:31:00.000	2984742	LTL
Johnson	2017-03-02 12:00:00.000	3084475	Truckload
IAC	2017-02-01 14:30:00.000	4184725	LTL
Brose	2017-12-30 12:01:00.000	1054445	Truckload

In load script,

Year(PickupDate)   as ServiceYear,

Month(PickupDate) as MonthName,

Ceil(Month(PickupDate)/3) AS Quarter.

In Bar char I'm taking MonthName and  ServiceYear as dimensions.

Now, i want restrict the records which are future date.( Johnson and Brose).

because those records are has future data. I want show records till current month only.

sample dashboard.

Thanks,

ASHOK

swuehl‌stalwar1‌vinieme12‌adamdavi3s‌

[sunny_talwar]

We are not seeing anything for March 2017 and April 2017, what exactly are you trying to exclude any record for Johnson and Brose?

[MayilVahanan · Accepted solution]

Hi

If you want to load only upto today data, Try like this,

In Script

Load *,

Year(PickupDate)   as ServiceYear,

Month(PickupDate) as MonthName,

Ceil(Month(PickupDate)/3) AS Quarter

From yourtablename

Where Floor(PickupDate) <= Floor(Today());

[vinieme12]

Try

Count({<ServiceYear = {$(=Year(Today()))},MonthName = {"<$(=Month(Today()))"}>}Mode)

also read

Dates in Set Analysis

[adamdavi3s]

I assume you want something like this as your expression

Count({<PickupDate = {"<=$(=date(today()))"}>}[Order Number])

[sdmech81]

HI

Try adding in ur set expression something like:

{<Pickupdate={'<=date(today())'}>}

Just try them by restricting with some condition like above

Sachin

[sdmech81]

This I sadditional modifier u should add along with the existing.

Sachin

[sunny_talwar]

I see those little yellow bars when I opened the image. I would just create a flag in the script and probably do it that way

LOAD Customer,

          PickupDate,

          Year(PickupDate)   as ServiceYear,

          Month(PickupDate) as MonthName,

          Ceil(Month(PickupDate)/3) AS Quarter

          [Order Number],

          Mode,

          If(PickupDate > Today(), 0, 1) as Flag

FROM ....;

and then this

Sum({<Flag = {1}>} Measure)

[KCC]

Hello, I am having problem with this. So I have one expression on the filter I created for 24 months, and by default it shows me the future dates. I want to restrict it on my expression, my current expression is: 

=[Day-Month]