How do you calculate STDEV of past four dates, using totals, instead of individual values?

[scheibercamo]

Hi -

I am trying to calculate the Standard Deviation of the last four dates to color each bar a different color. Whenever I try to calculate the Standard Deviation of all four dates, Qlik keeps on calculating as if I am looking at individual field values (each date is powered by several hundred entries).

I am simply trying to calculate the standard deviation of the numbers at the top of the bar chart: 45.32, 27.06, 41,26, and 52.57.

The field name powering the totals is "Sum_Total", and the date field is "Date".

When I try = STDEV({<Date=>}Sum_Total), thinking that I would be able to disregard every date field, I get a very low number.

Any way to group the sum of each date and then calculate the standard deviation separately? I want to be able to power the color of each bar chart based on if it is a certain number of units above the standard deviation, below, etc.

Thanks!

Ricky

[sunny_talwar]

May be like this

Stdev({<Date>} Aggr(Sum({<Date>} Sum_Total), MonthYear))

[scheibercamo]

I just tried Sunny, thanks. It doesn't work - what does the "MonthYear" stand for, is that a calculated field?

[sunny_talwar]

The dimension of your chart ... I hope it is not a calculated dimensions.... because if it is, then I would suggest you to create a field in the script and use that in the Aggr() function

[scheibercamo]

It is a calculated field, which I wrote in the back-end:

date(Raw_Date,'MMM-YY') as Date

How to I add AGGR to this?

[sunny_talwar · Accepted solution]

Use this

Date(MonthStart(Raw_Date), 'MMM-YY')) as MonthYear

and now use the expression

Stdev({<Date>} Aggr(Sum({<Date>} Sum_Total), MonthYear))

[scheibercamo]

awesome - I think it works!!

[sunny_talwar]

Nice