topic Re: Rolling 12 Months Chart in QlikView

Rolling 12 Months Chart

cbaqir — Thu, 06 Sep 2018 16:57:47 GMT

I have two charts that should show the same numbers since I have less than 12 months of data but for some reason, I am getting a lower number in my last month when using:

count({1<TRX_DATE={">=$(=addmonths(monthend(max(TRX_DATE))+1,-12)) <=$(=max(TRX_DATE))"}>}distinct FIRING_RECORD)

Than when just using

count(distinct FIRING_RECORD)

My current data set is from 12/1/2017 - 8/26/2018. How do I ensure that both graphs show the same number in the month of August?

Re: Rolling 12 Months Chart

sunny_talwar — Thu, 06 Sep 2018 16:59:18 GMT

Is TRX_DATE a timestamp field may be?

Re: Rolling 12 Months Chart

cbaqir — Thu, 06 Sep 2018 19:04:17 GMT

It might but in the load script I have: date(TRX_DATE, 'MM/DD/YYYY') as TRX_DATE

Re: Rolling 12 Months Chart

sunny_talwar — Thu, 06 Sep 2018 19:28:14 GMT

Date is just a formatting function... try this

date(Floor(TRX_DATE), 'MM/DD/YYYY') as TRX_DATE

Re: Rolling 12 Months Chart

cbaqir — Thu, 06 Sep 2018 19:34:57 GMT

Do you have any idea how I can turn email notifications of posts on my threads? Somehow it got turned off.

Re: Rolling 12 Months Chart

sunny_talwar — Thu, 06 Sep 2018 19:39:39 GMT

Not entirely sure, but may be you have to follow the thread

Re: Rolling 12 Months Chart

cbaqir — Thu, 06 Sep 2018 19:43:36 GMT

That did not fix it.

Re: Rolling 12 Months Chart

cbaqir — Thu, 06 Sep 2018 20:03:29 GMT

count(DISTINCT FIRING_RECORD) vs count({1<TRX_DATE={">=$(=addmonths(monthend(max(TRX_DATE))+1,-12)) <=$(=max(TRX_DATE))"}>}distinct FIRING_RECORD)

Re: Rolling 12 Months Chart

maxsheva — Thu, 06 Sep 2018 22:15:31 GMT

Hi would suggest to investigate last month dates.

Try to add TRX_DATE as dimension in straight table as well as two expressions. Then add max(TRX_DATE) somewhere in text box in order to look at this value.

Probably it will give you idea where issue is.