Using combitation of sum, median, aggr, sum

[turodionova]

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!

[#2]

Hi,

See attached,

I modified the script slightly to flag the Median.

Hope it helps.

[turodionova]

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)

[#4]

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]

Hi, zohaibirshad!

Thank a lot for your effort!

Kind regards.

Tamara