date range

[bobbydave]

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)

[Anonymous]

Hi David,

Did you tried with Qlik "stdev" function?

-Jai

[bobbydave]

Hi Jai.

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

[bobbydave]

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