Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
cbaqirdhds
Valued Contributor

Rolling 12 Months Chart

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?

Tags (1)
8 Replies

Re: Rolling 12 Months Chart

Is TRX_DATE a timestamp field may be?

cbaqirdhds
Valued Contributor

Re: Rolling 12 Months Chart

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

Re: Rolling 12 Months Chart

Date is just a formatting function... try this

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

cbaqirdhds
Valued Contributor

Re: Rolling 12 Months Chart

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

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

Capture.PNG

cbaqirdhds
Valued Contributor

Re: Rolling 12 Months Chart

That did not fix it.

cbaqirdhds
Valued Contributor

Re: Rolling 12 Months Chart

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

2018-09-06_13-02-29.jpg

maxsheva
Contributor II

Re: Rolling 12 Months Chart

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.