Set analysis : Cumulative data to year to date

[YCH]

Hello everyone,

Hope you’re safe and healthy.

I get data from cumulative format and I want to manage it with using YTD format to display on char no cumulative values.

The table below is a example of input data. As you can see, in February column I get the sum of the sales for January and February.

	Jan	Feb	Mar	Apr	May	Jun
Input Data Sales	1	12	24	30	45	70
YTD Sales	1	11	12	6	15	25

I try to manage it on the script but I have a lot of dimension and analysis axis and the loading took a long time.

Do you have a set analysis that allow me to manage this ?

Thanks you !

[QFabian]

Hi, please give us an example of expected chart 

[YCH]

Hi,

A simple bar chart like this

Or a simple table with month as dimension and YTD Sales as measure

Thank you

[QFabian]

Ok, try creating a bar chart with Month as dimension and this as expression :

if(RowNo()=1, [Input Data Sales],
[Input Data Sales] - above([Input Data Sales]))

let me know

[YCH]

It works if I selected only one year without month but if I selected more than one year or one month different thant january, for example march and april, it doesn't not work.

The values display are cumulative values and not YTD values

[QFabian]

so you need to do some script to solve all the problem.

try using peek and previous functions.