topic Re: Looking back 6 months using a Load Date and Start Date in QlikView

Looking back 6 months using a Load Date and Start Date

Wed, 04 Jan 2017 22:10:07 GMT

Hello:

I am having a tough time with a calculation. I do not have a calendar in my Dashboard, but the data I am using has dates. I am looking to use a field called 'Load Date' and look to see how many items were created in the past 6 months. I would be using another field named 'Issue Start Date'. I would need a count of those items that were started within 6 months of the Load Date. Is this possible?

Thank you for your time.

Re: Looking back 6 months using a Load Date and Start Date

tchovanec — Wed, 04 Jan 2017 23:14:09 GMT

Are you looking for items within 6 months prior and 6 months after the load date?

Re: Looking back 6 months using a Load Date and Start Date

Thu, 05 Jan 2017 00:28:28 GMT

Hi Thomas,

I am only looking for the past 6 months worth of data. Current to 6 months prior only.

Re: Looking back 6 months using a Load Date and Start Date

parimikittu — Thu, 05 Jan 2017 05:27:24 GMT

Hi, Please use the below code and let me know.

"count({}items) "

Re: Looking back 6 months using a Load Date and Start Date

Thu, 05 Jan 2017 05:53:24 GMT

Hi ,

May be like this

count({$<LoadDate={">=$(=MonthStart(AddMonths(Today(),-6)))<=$(=MonthEnd(AddMonths(Today())))"}>} Items)

Re: Looking back 6 months using a Load Date and Start Date

Thu, 05 Jan 2017 11:12:59 GMT

Hi Partha:

Thank you for your response. Is it possible to get a distinct count using this formula? I have duplicates in the source data. My formula is as follows:

count(Distinct({$<[Load Date]={">=$(=MonthStart(AddMonths(Today(),-6)))<=$(=MonthEnd(AddMonths(Today())))"}>} [Issue ID]))

The formula without the Distinct works well.

Re: Looking back 6 months using a Load Date and Start Date

Thu, 05 Jan 2017 16:21:56 GMT

I have tried the following formula and it doesn't work.

count(Distinct({$<[Load Date]={">=$(=MonthStart(AddMonths(Today(),-6)))<=$(=MonthEnd(AddMonths(Today())))"}>} [Issue ID]))

Any help would be greatly appreciated!

Thank you

Re: Looking back 6 months using a Load Date and Start Date

Thu, 05 Jan 2017 16:28:43 GMT

The Calculation should be :

count(Distinct{$<[Load Date]={">=$(=MonthStart(AddMonths(Today(),-6)))<=$(=MonthEnd(AddMonths(Today())))"}>} [Issue ID])

I had to remove the ( from after Distinct and the ) from the end.