Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Highlighted
naiduvc467
New Contributor

MTD expression doesn't work

Hello All,

My MTD expression works ok if I don't put Date field in the dimension, but I want to see the count of attendances (MTD )on each day.

 

count({<[Date]={">=$(=(Date(Monthstart(Max(Date)),'YYYY-MM-DD')))<=$(=(Date(Max(Date)-1,'YYYY-MM-DD')))"},_Month=,[_Fiscal Year]=,[IS4hrEDBreach]={'No'},Type={'AandE'}>}ID)

I have gone through so many posts in the community  for help tried my best before I post it here, please let me know what I am missing here.

 

Thanks

 

Labels (2)
1 Solution

Accepted Solutions

Re: MTD expression doesn't work

Are you using RowNo() at the end or DayField? You need to use DayField or Day(Date) so that the accumulation restarts at the end of the month.
4 Replies

Re: MTD expression doesn't work

Try this

RangeSum(Above(Count({<[Date], _Month, [_Fiscal Year],[IS4hrEDBreach] = {'No'}, Type = {'AandE'}>} ID), 0, DayField))

or if you don't have a day field, you can try this...

RangeSum(Above(Count({<[Date], _Month, [_Fiscal Year],[IS4hrEDBreach] = {'No'}, Type = {'AandE'}>} ID), 0, Day(Date)))

  

naiduvc467
New Contributor

Re: MTD expression doesn't work

Hello Sunny,

Thanks for your quick reply, I might have written it wrong actually I want it for each date in a full month I did tried with range sum but if I use range sum and if I want to show rolling 7 days it's summing few days from previous month aswell.

I hope below image will be useful in knowing what I am trying to achieve. I want to show rolling 7 days of MTD.

Capture.PNG

Re: MTD expression doesn't work

Are you using RowNo() at the end or DayField? You need to use DayField or Day(Date) so that the accumulation restarts at the end of the month.
naiduvc467
New Contributor

Re: MTD expression doesn't work

Thanks Sunny it works