topic Re: How to achieve this in QlikView

How to achieve this

Sun, 08 Nov 2015 18:42:03 GMT

My data is has following cols

Country, State, Period(in mm/dd/yyyy format), Qty

A, A1, 1/1/2015, 10

A, A2, 1/1/2015, 20

A, A1, 2/1/2015, 25

A, A1, 1/1/2014, 15

A, A1, 2/1/2014, 10

B, B1, 3/1/2015, 30

I want to achieve the following output

Year(Period) Country Sum(Qty) (for the Year) Average Qty of each but the based on MONTHLY aggregation

2015 A 10+20+25=55 Average of (10+20) and 25 = 55/2

2015 B 30 30/1 = 30

2014 A 15+10=25 Average of 15 and 10 = 25/2

I assume Aggr will have to be put to use - tried few options but gettig "-" as the answer.

In addition to Average, I also want to get the Standard Deviation.

Any help much appreciated.

Thanks,

Gopal

Re: How to achieve this

oknotsen — Sun, 08 Nov 2015 20:04:11 GMT

I suggest adding a Year and a Month field to your data model based on that Period field.

Assuming you did that, you are going to make a Table with 2 dimensions:

- Year

- Country

You are going to create two expressions:

- sum(Qty)

- avg(aggr(sum(Qty), Year, Month))

Good luck!

Re: How to achieve this

maxgro — Sun, 08 Nov 2015 20:58:44 GMT

if your Period is always (at it seems from your table) on 1st of month

sum(Qty)

avg(aggr(sum(Qty),Period,Country))

Re: How to achieve this

Anonymous — Mon, 09 Nov 2015 03:12:41 GMT

Here's a non-aggr solution:

ExampleData:

Load

Year (PeriodDate) as PeriodYear,

Year (PeriodDate) & '-' & Month(PeriodDate) as PeriodMonth

Inline

[

Country, State, PeriodDate, Qty

A, A1, 1/10/2015, 10

A, A2, 1/10/2015, 20

A, A1, 2/10/2015, 25

A, A1, 1/10/2014, 15

A, A1, 2/10/2014, 10

B, B1, 3/10/2015, 30

];

In a straight table with country and state as dimensions, add two expressions:

sum(Qty)

sum (Qty) / count (distinct PeriodMonth)

For the standard deviation, easiest to use aggr:

sum(Aggr(stdev(Qty),PeriodYear,Country))

With Aggr, always make sure you have an aggregate function outside of the aggr. So you need:

AggregationFunction1(Aggr(AggregationFunction2(field)))

Without the outside aggregation function you may receive a hyphen. Here a hyphen frequently represents more than one value. The outside aggregation makes sure you're returning only one value.

Re: How to achieve this

Mon, 09 Nov 2015 19:23:02 GMT

Added two dimensions i.e. Year and Year-Month to the data as aggr doesn't seem to work on expressions and then applied the avg expression as suggested. it works.

Re: How to achieve this

Mon, 09 Nov 2015 19:24:29 GMT

Also a correct answer. instead of inline col, i added the two cols in the input data