Cumulated measure

[Anonymous]

Hi guys,

I've a necessity to build a chart with an cumulative calculation.

Dimensions: Container id, Date

Measures: PRI = if day(Date) = 1 then 1 else TWRR * previous(PRI) end

TWRR is another calculation and can be disregarded.

How to implement this calculation?

Thanks.

[Anonymous]

The initial PRI value is 15 and this is constant.

The CONST is 1.5 in the example.

But in the reality it changes for each container and each date. Though, it does not matter, I put it here just to show that we have some calculation and not just a simple reference to the previous value.

[sunny_talwar]

Seems to be working:

[PrashantSangle]

good one bro

[Anonymous]

Yes, indeed, it seems to be working. I wonder how come it is not working in my case... I'll keep investigating...

[sunny_talwar]

Are you making selections in Date field? This will tend to break with selections in date. By break I mean that the values will change and won't remain the same

[Anonymous]

I know this. And as for the time being this is fine.

Though, I wonder now if I can use this formula or a similar one in the Aggr function.

See, my actual problem is that I have a table with Container, Product and Date. And one calculation on the Product level requires this PRI calculation on the Container level.

So, the formula would look like this: TWRR (which is on container, product, date) * PRI (which is on container, date).

So, this would eventually be something like:

=TWRR * sum( aggr( [PRI CALCULATION HERE], Container, Date ))

That is why I was initially trying to replicate this calculation in the straight table to check its general feasibility.

Would that be possible?

[sunny_talwar]

You concern is valud and I don't think this will work within Aggr(). Don't knowing your data very well, but still have a feeling that we might be able to avoid Aggr. To test this out, we would need some sample data as close to your actual data. It would also be helpful to have the expected output so that we can aim to match it up with the actual data.

[Anonymous]

In the meanwhile, I found the reason why the Above did not work in my case: I had 3 dimensions in the straight table. Above works with 2 at max. So weird... 😕

[Anonymous]

And

Load 1 as T, *;

Load * INLINE [....];

for appending new fields is so helpful too. Thanks for it.

[sunny_talwar]

You can try this:

If(Day(Date) = 1, PRI, 1.5 * Above(TOTAL PRI_Label))