topic Re: Using combitation of sum, median, aggr, sum in QlikView

Using combitation of sum, median, aggr, sum

turodionova — Thu, 01 Aug 2013 08:33:40 GMT

Hello,

Could somebody help me with the following:

I have a table: Product, Category, Store, Date, Sales, MonthYear.

I need to calculate sum of median of every Product sold in every Store calculated for all 'MonthYear' grouped by Category.

With another words, first i group my table by Product, Category, Store, MonthYear, sum(Sales),

then calculate for every Product and Store median within all MonthYear,

and Finally summarize by Category.

In the attached file please find my QV file with the table and Excel with step by step calculation.

I tried to do the first two steps by myself =median(aggr(sum(Sales), MonthYear,Store,Product))

But i realize that my expression calculates median across all aggregated group .

Many thanks in advance!

Thu, 01 Aug 2013 10:50:01 GMT

Hi,

See attached,

I modified the script slightly to flag the Median.

Hope it helps.

turodionova — Thu, 01 Aug 2013 11:59:30 GMT

Hi, zohaibirshad!

Thank you for answer!

I have a couple of questions:

1) Is it possible to do this without script modification - with expression

only, as i have to save data structure in original application ("date"

field )?

2) How can i finally get results - a chart with sum by Category?

Kind regards

Tamara Rodionova

Colgate-Palmolive Russia.

Senior Sales Analyst

Office phone +7495 937 11 00 (ext. 2-822)

Thu, 01 Aug 2013 14:18:12 GMT

Hi Tamara,

Please see attached updated version.

I was unable to do it in the expression. As a result I built a Link Table that has aggregated and non-aggregated data (non-aggregated data has dates)

Hope this helps.

Thanks

turodionova — Tue, 06 Aug 2013 06:27:15 GMT

Hi, zohaibirshad!

Thank a lot for your effort!

Kind regards.

Tamara