Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ashokpaladugula
Contributor III
Contributor III

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

Hi all,

CustomerPickupDateOrder NumberMode
Nexteer2015-01-01 18:15:00.0002784436Truckload
Regal2015-01-02 06:15:00.0002744436LTL
Robert2016-07-12 18:00:00.0002254436Truckload
MASCO2016-07-18 12:31:00.0002984742LTL
Johnson2017-03-02 12:00:00.0003084475Truckload
IAC2017-02-01 14:30:00.0004184725LTL
Brose2017-12-30 12:01:00.0001054445Truckload

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.

Untitled.png

Thanks,

ASHOK

swuehlstalwar1vinieme12adamdavi3s

1 Solution

Accepted Solutions
MayilVahanan

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());

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

8 Replies
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

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());

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
vinieme12
Champion III
Champion III

Try

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

also read

Dates in Set Analysis

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
adamdavi3s
Master
Master

I assume you want something like this as your expression

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

sdmech81
Specialist
Specialist

HI

Try adding in ur set expression something like:

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

Just try them by restricting with some condition like above

Sachin

sdmech81
Specialist
Specialist

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
Contributor II
Contributor II

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]