topic Re: MTD expression doesn't work in QlikView

MTD expression doesn't work

naiduvc467 — Sat, 16 Nov 2024 04:48:08 GMT

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

Re: MTD expression doesn't work

sunny_talwar — Thu, 10 Jan 2019 11:56:46 GMT

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

Re: MTD expression doesn't work

naiduvc467 — Thu, 10 Jan 2019 12:16:15 GMT

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.

Re: MTD expression doesn't work

sunny_talwar — Thu, 10 Jan 2019 12:19:58 GMT

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.

Re: MTD expression doesn't work

naiduvc467 — Thu, 10 Jan 2019 12:42:26 GMT

Thanks Sunny it works