How to calculate a MTD in the Script?

[Tatynout]

Hello, 

I would like to make a MTD (monthly to date) calculation of data by cumulating monthly data from 2018 in QS script. 

Here is an example of the calculation in Excel:

Here is how the data should be represented in QS :

I am looking for a way to  make the MTD calculation as it is done in my excel file into a QS Script.  Please help.

I have attached my excel file to this post.

Thanks in advance for your help.

Tatiana

[aveeeeeee7en]

Hi @Tatynout 

Try this in your script:

RangeSum([Monthly Value],Peek('MTD')) AS MTD

Let me know if in case it doesn't work for you.

Note: In case you are getting some error in Sum than make sure to use Order By in your table (The Resident one).

Regards,

Av7eN

[Tatynout]

Hi @aveeeeeee7en ,

Thank you for your reply. The column MTD in my excel file was just an illustration of the calculation i would like to reproduce into the script. I do not have that column into my original source file and i need to calculate my MTD into Qlik Sense.

Do you please have an idea on how that column calculated in Qlik Sense?

Thanks for your reply.

Regards,

Tatiana

[aveeeeeee7en]

Hi @Tatynout 

Have you tried the code in your script. It will work.

I'm creating that new column (MTD) and calling it inside the logic by using Peek function.

Regards,

Av7eN

[Tatynout]

Hi @aveeeeeee7en ,

Yes I have tried your code but I do not achieve the correct result on my bar chart  object. Data are not being cumulated monthly.

Regards,

Tatiana

[aveeeeeee7en · Accepted solution]

Hi @Tatynout 

I've tried on my system. It's working. See this:

TEST:
LOAD *, RangeSum([Monthly Value],Peek('MTD')) AS MTD INLINE [
Year, Month, Monthly Value
2020, 8, 25
2020, 9, 35
2020, 10, 15
2020, 11, 10
2020, 12, 67
2020, 1, 85
2020, 2, 75
2020, 3, 26
2020, 4, 98
];

If you've some smaple than kindly share here so that I can work on it.

Regards,

AV7eN

[Tatynout]

Hi @aveeeeeee7en 

It works just fine now. thank you very much for your help  😊

Regards,

Tatiana

[aveeeeeee7en]

Your Welcome @Tatynout 

I'm glad that the logic worked for you.

Regards,

Av7eN