date range

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

sum([record count])

We get data from the previous month

Month(AddMonths(Today(),-1)) = Oct 2016

I am then trying to do a standard deviation of the sum of the data from the previous 6 months before October.

So what the expression below should do is get the months between April and Sept.

My expression is something like

=sum({< Date =

// range from 6 months previous and the month previous to current month

{' >=$(=Month(AddMonths(Today(),-7)))<=$(=Month(AddMonths(Today(),-2))) '} >}

[Record Count])

Now I want the standard deviation of this

=sum({< Date =

// range from 6 months previous and the month previous to current month

{' >=$(=Month(AddMonths(Today(),-7)))<=$(=Month(AddMonths(Today(),-2))) '} >}

[Record Count])/6

From here, I want to see what percentage this deviation by last months figure is

{sum( {< month(addMonths(Today(), -7)) >} [Record Count])) /

(sum({< Date =

// range from 6 months previous and the month previous to current month

{' >=$(=Month(AddMonths(Today(),-7)))<=$(=Month(AddMonths(Today(),-2))) '} >}

[Record Count])/6)

and if this value is below a certain threshold, it will throw back an alert.

Is my logic correct to get the last 6 months?

{sum( {< month(addMonths(Today(), -7)) >} [Record Count])) /

(sum({< Date =

// range from 6 months previous and the month previous to current month

{' >=$(=Month(AddMonths(Today(),-7)))<=$(=Month(AddMonths(Today(),-2))) '} >}

[Record Count])/6)

Re: date range

Anonymous — Thu, 24 Nov 2016 12:12:22 GMT

Hi David,

Did you tried with Qlik "stdev" function?

-Jai

Re: date range

bobbydave — Thu, 24 Nov 2016 12:15:49 GMT

Hi Jai.

Its not so much the stddev, its trying to capture the date ranges which is my problem.

Re: date range

bobbydave — Thu, 24 Nov 2016 14:03:52 GMT

If Fiscal Year Month is 2016-10

Then I should be getting >= 2016-04 <= 2016-09

=Sum( {< Country,

[Fiscal Year Month]= {">=$(=Date(AddMonths(Date#([Fiscal Year Month], 'YYYY-MM'), -6), 'YYYY-MM'))

<=$(=Date(AddMonths(Date#([Fiscal Year Month], 'YYYY-MM'), -1), 'YYYY-MM'))" }

[Record Count])

Still get a value of 0

topic Re: date range in QlikView

date range

Re: date range

Re: date range

Re: date range