Aggregate chart expression problem

Thu, 15 Mar 2012 06:06:52 GMT

Hi,

I am having trouble with aggregation of chart expressions.

My client wants a table that displays Stock Turns by StockItem. The calculation for this is CostOfSales/CostOnHand. It is normally a value between 0 and 1 (eg 0.12).

The data is stored in a StockPeriod table, which gives us those two values for every StockItem, Location and Month.

For example:

StockPeriod:

LOAD * Inline [

MonthStart,StockItem,Location,CostOfSales,CostOnHand

1/01/2012,Product1,Location1,10,100

1/01/2012,Product1,Location2,12,100

1/01/2012,Product1,Location3,8,120

1/01/2012,Product2,Location1,30,335

1/01/2012,Product2,Location2,61,296

1/01/2012,Product2,Location3,70,301

1/01/2012,Product3,Location1,82,717

1/01/2012,Product3,Location2,22,828

1/01/2012,Product3,Location3,36,513

1/02/2012,Product1,Location1,87,509

1/02/2012,Product1,Location2,26,881

1/02/2012,Product1,Location3,39,895

1/02/2012,Product2,Location1,88,44

1/02/2012,Product2,Location2,12,691

1/02/2012,Product2,Location3,75,991

1/02/2012,Product3,Location1,84,420

1/02/2012,Product3,Location2,97,709

1/02/2012,Product3,Location3,46,465

1/03/2012,Product1,Location1,80,556

1/03/2012,Product1,Location2,68,794

1/03/2012,Product1,Location3,72,536

1/03/2012,Product2,Location1,43,553

1/03/2012,Product2,Location2,4,874

1/03/2012,Product2,Location3,38,93

1/03/2012,Product3,Location1,86,558

1/03/2012,Product3,Location2,80,441

1/03/2012,Product3,Location3,63,990];

When calculating the Stock Turns for a StockItem, the values should be totalled across all Locations, the ratio calculated, then added together for each month.

Given the data above, I can calculate the correct results using a hard-coded Set Analysis expression as follows:

Sum({$<MonthStart={'1/01/2012'}>} CostOfSales) / Sum({$<MonthStart={'1/01/2012'}>} CostOnHand)
+
Sum({$<MonthStart={'1/02/2012'}>} CostOfSales) / Sum({$<MonthStart={'1/02/2012'}>} CostOnHand)
+
Sum({$<MonthStart={'1/03/2012'}>} CostOfSales) / Sum({$<MonthStart={'1/03/2012'}>} CostOnHand)

Obviously this will not work for production, but I cannot develop the correct Aggr expression to make it work dynamically.

Any help is very welcome. I will attach my sample app.

Re: Aggregate chart expression problem

jagan — Thu, 15 Mar 2012 08:15:47 GMT

Hi,

Check the attachment for solution, hope it helps you.

The expression used is

=Sum(Aggr(Sum(CostOfSales)/Sum(CostOnHand), MonthStart, StockItem))

Regards,

Jagan.

Re: Aggregate chart expression problem

Fri, 16 Mar 2012 00:30:40 GMT

Thanks for your help Jagan.

That does work for the table I showed in my original post.

It does not work, however, if you add the Location as a dimension. I was trying to implement Location as a conditional dimension and allow the client to turn it on and off with a button. Instead of this, I have created two seperate tables with different expressions and put them in a Container. Also added a third table to the container to show them the breakdown by Month.

Overall, I have given the client more information than they requested, but hopefully they can use it to validate the calculations and use it with confidence.

Regards,

Michael

topic Re: Aggregate chart expression problem in QlikView

Aggregate chart expression problem

Re: Aggregate chart expression problem

Re: Aggregate chart expression problem