topic Re: How to restrict data up to current month in present year. in QlikView

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

ashokpaladugula — Wed, 25 Nov 2020 16:16:04 GMT

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‌

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

sunny_talwar — Wed, 15 Feb 2017 10:12:20 GMT

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

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

MayilVahanan — Wed, 15 Feb 2017 10:14:10 GMT

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

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

vinieme12 — Wed, 15 Feb 2017 10:16:17 GMT

Try

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

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

adamdavi3s — Wed, 15 Feb 2017 10:17:26 GMT

I assume you want something like this as your expression

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

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

sdmech81 — Wed, 15 Feb 2017 10:22:05 GMT

Try adding in ur set expression something like:

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

Just try them by restricting with some condition like above

Sachin

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

sdmech81 — Wed, 15 Feb 2017 10:22:51 GMT

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

Sachin

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

sunny_talwar — Wed, 15 Feb 2017 10:24:25 GMT

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)

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

KCC — Wed, 12 Oct 2022 09:39:39 GMT

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]