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: 
anjali0108
Partner - Creator III
Partner - Creator III

Closed on this month

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.

12 Replies
kamal_sanguri
Specialist
Specialist

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.

kamal_sanguri
Specialist
Specialist

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).

vinieme12
Champion III
Champion III

can you post a sample app?

Preparing examples for Upload - Reduction and Data Scrambling

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vinieme12
Champion III
Champion III

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}

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
anjali0108
Partner - Creator III
Partner - Creator III
Author

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.

Kushal_Chawda

this should work

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

vinieme12
Champion III
Champion III

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,

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
anjali0108
Partner - Creator III
Partner - Creator III
Author

Hi,

CommonDateNum is simply num(<my datefield>) .

vinieme12
Champion III
Champion III

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

have you tried below?

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


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