periodic analysis

[wwintz]

Hello,

I have two dimensions 'year' on columns and 'week of the year' on rows. In a periodic analysis I calculate the annual difference for each week. This works fine but with both methods the "Difference by Column" view and a calculated measure I dont get rid of the superfluous first (respectively last) difference. Is it possible to get N-1 differences from N dimensional columns?

Thanx

Wolfgang

[AlexZencovich]

Hello,

Could you describe problem little more detail? This may be my misunderstood with terms you have used - you always have N-1 differentces in N-dimensional set because last (N-diff) is result of Column(n)-0 or 0 - Column(0) (may be also Column(0) - 0 - this is controlled by TPivotMap.DDirection property)

Anyway, you may perfrom your own preriodical analysis (especially if you have sampling set or shedule) by use any from 2 ways

1. Use OnGetDataCell event handler to replace cell value with your own cell value, which may be calculated as Column(i) - Column(i-1) or Column(i) - Sheduled(i) etc

2. Use same way to custom calc. measure with evend handler placed to OnCalculateMeasure - you may avoid any formula for this measure and use event handler to fill calculate measure with your own cell value.

Please see demo app how to get cell values for different grid cells etc.

[wwintz]

Hello,

this is my starting point

year 2005 2006 total
quantity quantity quantity
week value value value
1 10 15 25
2 15 20 35
... ... ... ...

when I add the view 'Difference with previous Column' I get

year 2005 2006 total
quantity quantity quantity
week value Difference value Difference value Difference
1 10 -10 15 -5 25 -25
2 15 -15 20 -5 35 -35

when I add the calculated measure 'Difference':
Result = "quantity"[VALUE] - "quantity"[PREV_BY_COL]
I get

year 2005 2006 total
quantity Difference quantity Difference quantity Difference
week value value value value value value
1 10 10 15 5 25 10
2 15 15 20 5 35 15
... ... ... ... ... ... ...

In both cases the '2005 Difference' is meaningless. Using the calculated measure 'Difference':
Result = "quantity"[NEXT_BY_COL] - "quantity"[VALUE]
the '2006 Difference' becomes meaningless. Also the meaning of 'total Difference' is not quite clear.

My ambition is to get something like

year 2005 2006
quantity quantity Difference Difference % Running %
week value running value running value running value value
1 10 10 15 15 5 5 50 50
2 15 25 20 35 5 10 33 40
... ... ... ... ... ... ... ...
^----------with this part repeating in 2007-----------^

without any special report programming. I think all the calculations should work but I don't get rid of the 2005 differences.

[AlexZencovich]

Hmm, it is non-relational operations, which should be applied by condition like "if column is N then operator A applied else if column is M then operator B applied else....etc"

Pity but you cannot hide difference value for 2005 anyway.

I may suggest yopu to put another calc. measure, based on calc. measure "Difference" to get % value for current difference related to quantity value, like

Difference%

Result = "Difference" / "Quantity"

[wwintz]

Hello,

I tryed this with the following measures:

"running sum":
Result = "quantity"[RUNNING_TOTAL_BY_COL]

"difference":
Result = "quantity"[VALUE]-"quantity"[PREV_BY_COL]

"change %":
Result = 100*"difference"[VALUE]/"quantity"[PREV_BY_COL]

When I added the last measure

"running change %":
Result = 100*("running sum"[VALUE]-"running sum"[PREV_BY_COL])/"running sum"[PREV_BY_COL])

something strange happened. The values of the first "running sum" column changed as "quantity"[RUNNING_TOTAL_BY_COL] -> "quantity"[VALUE] the second "running sum" column remained unchanged and the second "running change %" column was erroneously (at least to my expectation) calculated with respect to this inconsistent values in "running sum"[PREV_BY_COL]. I did not expect referencing a calculated measure to change its definition.

Comming back to the superfluous differences problem. Yes, it is a kind of a non-relational operation but a quite special and to my oppinion useful one. You must allready have some special code for the [PREV_BY_COL] view of the first column ...

[AlexZencovich]

If error you have got while add new measure continuos and reprodusable, please send to me screenshots how you add new measures and outputs. Pity, but I have tried add same formulas to demo and have not any problem with it.

According to'special' code for first column - I expect to have same number of columns for each measure, in each column dimnsion tem. Otherwise we can got situation when user HIDE view VALUE and only DiffwithPrevColumn still visible. It mean - first dimnsion item should disappear at all or became visible (all zeros) - it will confuse user anyway and probably be reason for wrong decision (user will treat first column for second or second for first etc).

Moreover, if user make swap row->columns it will mean all first row should disappear to produce expected output. But there may be more than one measure in grid so these measures should not be removed.