Ignore Date dimensions

[#1]

Hello,

I have the following table of data:

Posting Date	Store	Quantity
01.01.2013	Big	100
01.01.2013	Big	-30
01.02.2013	Big	-10
10.02.2013	Big	-12
15.02.2013	Small	105
15.02.2013	Small	-25
20.02.2013	Big	40
18.03.2013	Small	-5
27.03.2013	Small	-2
06.04.2013	Small	45
01.01.2014	Big	-50
10.01.2014	Big	-20
15.02.2014	Small	-40
24.02.2014	Small	-10
01.03.2014	Big	-11
15.04.2014	Small	-6

What I need is Inventory to date, month or year for every Store. Like this:

			Store				Calculates
Year	Month	Posting Date	Big	Small	Grand Total		Big	Small
2013			88	118	206		=100-30-10-12+40	=105-25-5-2+45
	1		70	0	70		=100-30	=0
		01.01.2013	70	0	70		=100-30	=0
	2		88	80	168		=100-30-10-12+40	=105-25
		01.02.2013	60	0	60		=100-30-10	=0
		10.02.2013	48	0	48		=100-30-10-12	=0
		15.02.2013	48	80	128		=100-30-10-12	=105-25
		20.02.2013	88	80	168		=100-30-10-12+40	=105-25
	3		88	73	161		=100-30-10-12+40	=105-25-5-2
		18.03.2013	88	75	163		=100-30-10-12+40	=105-25-5
		27.03.2013	88	73	161		=100-30-10-12+40	=105-25-5-2
	4		88	118	206		=100-30-10-12+40	=105-25-5-2+45
		06.04.2013	88	118	206		=100-30-10-12+40	=105-25-5-2+45
2014			7	62	69		=100-30-10-12+40-50-20-11	=105-25-5-2+45-40-10-6
	1		18	118	136		=100-30-10-12+40-50-20	=105-25-5-2+45
		01.01.2014	38	118	156		=100-30-10-12+40-50	=105-25-5-2+45
		10.01.2014	18	118	136		=100-30-10-12+40-50-20	=105-25-5-2+45
	2		18	68	86		=100-30-10-12+40-50-20	=105-25-5-2+45-40-10
		15.02.2014	18	78	96		=100-30-10-12+40-50-20	=105-25-5-2+45-40
		24.02.2014	18	68	86		=100-30-10-12+40-50-20	=105-25-5-2+45-40-10
	3		7	68	75		=100-30-10-12+40-50-20-11	=105-25-5-2+45-40-10
		01.03.2014	7	68	75		=100-30-10-12+40-50-20-11	=105-25-5-2+45-40-10
	4		7	62	69		=100-30-10-12+40-50-20-11	=105-25-5-2+45-40-10-6
		15.04.2014	7	62	69		=100-30-10-12+40-50-20-11	=105-25-5-2+45-40-10-6

I have Year, Month, Posting Date and Store as Dimensions. This makes useless Set Analysis cause it works for the Selections only (I can deal with it). The problem is to ignore to some extent the Date dimensions and make the Inventory expression calculate the Quantity for all dates before or equal to the Date dimension at the current row.

Any ideas are welcome.

Thanks for Your time!

Best Regards,

Plamen

[gerhard_jakubec]

Hi Plamen,

attached example does not fill your requirement but may be a starting point. Usually you use set analysis to ignore dimensions, however this does only work for dimensions not utilized within a chart itself. You have to use RangeSum functions to calculate across elements of a chart dimension. Search the forum for "running total".

This is such a common requirement but unfortunaltely not quite as trivial as it might seem. Why? Although by use of RangeSum a running total can be calculated, if a user restricts data to 2014 your running total is no more.

The only solution would be to store running total as a fact row by Store and PostingDate. This would allow an expression like

sum(

    {$< PostDate={"$(=Max(PostDate))"} >}

    [QuantityRunningTotal]

)

So the very last value of QuantityRunningTotal for each Store will be summed up. Crucial for this approach would however be a load script that creates the required fact rows. Maybe someone can contribute an example script.

[#3]

Thank You, Gerhard!

There were some problems in the example Calculates columns - corrected.

But the required results were ok (blue font).

I have tried with RangeSum and Above and it does work correctly when a dimension is entirely collapsed.

But if You expand one row in a dimension, the values for the subtotals of the whole dimension become incorrect - they calculate as if there is no RangeSum but only Sum.

So, the results are ok only for the deepest expanded dimension. As seen in Your attached example, the values in both columns (+/- and Sdo2) are equal on the subtotal rows for Year and Month.

That is because the RowNo() returns values only for the deepest expanded dimension. The subtotals do not return value (or return NULL, I am not quite sure). You may add an expression "=RowNo()" and see the results in different Expand/Collapse variations.

The Set Analysis (as I understand it) includes all the set information in the curly brackets BEFORE the qualifiers (TOTAL, ALL, DISTINCT). So, You can manipulate calculation but You cannot ignore the dimension value of the current row. It can ignore just the SELECTED dimension values. The TOTAL qualifier on the other hand, can ignore the row dimension value. But it ignores it TOTALLY - once ignored, the dimension value cannot be used to limit the period.

I will try to use some kind of Dimensionality filter in the expression with the Above and RowNo approach but I don't know how to escape the Expand/Collapse issue.

Thanks again!

Best Regards,

Plamen

[miikkaqlick]

Hi!

I've solved exactly problem like this. I solved it in script so that I have warehouse amount for every day. Only downside is that you may get much data...

If you have just small amount of data, do this in script. i can provide sample script.

Br,

Miikka

Climber Finland

[#5]

Hi, Miikka,

Yes, the script is a solution in general.

But 30000 articles in about 1000 dates and 10 locations on average seems to be far beyond normal data table size.

That's why I am looking for other options.

Best Regards,

Plamen

[gerhard_jakubec]

Hi Miikka,

after some elaboration with Peek() and Previous() functions i managed to calculate a running total column within script. However an expression like sum( {$< PostDate={"$(=Max(PostDate))"} >} [QuantityRunningTotal] ) will NOT work. My current example looks like:

year     | +/- |  QuantityRunningTotal

2010    | 10 |     10

2011    |  -2 |       8

2012    |   5 |     13

Total    | 13 |     31

I still have to use if(Dimensionality()=0,... ) to correct the Total sum(QuantityRunningTotal).

Also my example script does only work disregarding the Store dimension, i did not yet manage to modify it accordingly. If you can share an example of your implementation, please do so as it may enable me to leverage my script. Thank you.

@Plamen

i mildly dislike expressions containing nested if statemens so i try to go down the road of precalculating the total qty per Store and Date. At this point i am not sure about the drawbacks and pitfalls of this approach however...

PS: some useful contributions

How to calculate running total?

Re: Re: How to calculate running total/ average while loading data.

Running Sum on Load Script

Running Total Calculation