topic Re: Closed on this month in QlikView

Closed on this month

anjali0108 — Mon, 05 Jun 2017 07:44:18 GMT

Hi Experts,

I am using below formula to showcase numbers that are closed on this month:

CommonDateNum={">=$(=Num(MonthStart(Max(CommonDateNum)))<=$(=Max(CommonDateNum))"

But I can see that there are still some tickets that are not coming after applying this condition,i thought that "=" is not working in the below condition,so I added -1 in the expression to consider all values.

CommonDateNum={">=$(=Num(MonthStart(Max(CommonDateNum))-1))<=$(=Max(CommonDateNum)-1)"

Please let me know if this condition is fine or do I need to change something....

Thanks in advance.

Re: Closed on this month

kamal_sanguri — Mon, 05 Jun 2017 08:00:12 GMT

Can you try this -

CommonDateNum={">=$(=Num(MonthStart(Max(CommonDateNum)))<=$(=Floor(Num(MonthEnd(Today()))))"

Just to check - if chaning it a bit makes any difference.. there seems no problem with your expression.

Re: Closed on this month

kamal_sanguri — Mon, 05 Jun 2017 08:12:04 GMT

Can you check if the format of date in missing items is just the date put them inside num() to see if you get any decimal part as well.

It is quite possible that those dates which are not falling under your range may have time part (which may not be visible because they might only set to show date and not time).

Re: Closed on this month

vinieme12 — Mon, 05 Jun 2017 08:15:07 GMT

can you post a sample app?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Closed on this month

vinieme12 — Mon, 05 Jun 2017 08:20:08 GMT

of a simpler approach would be add a flag in your table during load

Example

if(num(month(<DateField>)) = Num(Month(Today())) , 1,0) as CurrentMonthFlag

and use this flag in set analysis

CurrentMonthFlag = {1}

Re: Closed on this month

anjali0108 — Mon, 05 Jun 2017 08:48:49 GMT

Hey, Vineet

Thanks a lot, it worked but

I think I missed out something in my requirement.

Its like,

I have to share the data for current month but from 1st to today()-1 on every single day.

ON 31st : i have to share data from 1st till 30th,that's why I am not making this flag in my script.

I need to validate the data from 1st to 30th May as well. but still there is some prob with my expression so need help.

Re: Closed on this month

Kushal_Chawda — Mon, 05 Jun 2017 09:04:47 GMT

this should work

CommonDateNum={">=$(=MonthStart(Max(CommonDateNum)))<=$(=Max(CommonDateNum)-1)"}

Re: Closed on this month

vinieme12 — Mon, 05 Jun 2017 09:48:51 GMT

what is the format for CommonDateNum?

is it an Integer? i don't see any reason why your current expression would not work

The flag can be simplified as below

FABS(InMonthToDate(<DATEFIELD>,today()-1,0)) as MTDFLAG,

Re: Closed on this month

anjali0108 — Mon, 05 Jun 2017 09:57:12 GMT

Hi,

CommonDateNum is simply num(<my datefield>) .

Re: Closed on this month

vinieme12 — Mon, 05 Jun 2017 09:59:21 GMT

do any of them have timestamp values? then you need floor(<Datefield>)

have you tried below?

FABS(InMonthToDate(<DATEFIELD>,today()-1,0)) as MTDFLAG,

Re: Closed on this month

PrashantSangle — Mon, 05 Jun 2017 10:04:29 GMT

Hi,

What is your CommonDateNum Format??

Make modification as per CommonDateNum Format.

Use Date() to format your date in set analysis

max(CommonDateNum) -----> returns date in numeric format.

For debugging your script test output of your expression in text object

create 2 text object 1 with expression

=Num(MonthStart(Max(CommonDateNum))-1)

2nd with

=Max(CommonDateNum)-1

Both output format match with you CommonDateNum format.

Regards,

Prashant

Re: Closed on this month

anjali0108 — Mon, 05 Jun 2017 10:06:54 GMT

Yes, i tried this flag but when I am selecting "1",it is selecting "MAY 2017".

Yes,my date field has both date and timestamp in it.,

Re: Closed on this month

vinieme12 — Mon, 05 Jun 2017 11:03:03 GMT

then try as below

FABS(inmonthtodate(Timestamp#(<Datefield>,'DD/MM/YYYY H:mm:ss'),today()-1,0)) as MTDFLAG

Replace with your datefield format 'DD/MM/YYYY H:mm:ss'