16 Replies Latest reply: Jul 27, 2016 3:19 AM by Onno van Knotsenburg

# Sum values up to x numbers of months

Hello and thanks for helping me.

I want to sum the values of an excel spreadsheet but accumulated only up to x number of months, do not want the whole year. I have tried sum({\$<Date={'\$(=max([Accountdate]))'}>} budget) but it is not working,

• ###### Re: Sum values up to x numbers of months

You can try chart expression property for that, like:

• ###### Re: Sum values up to x numbers of months

Thank you tresesco, but i am using Qliksense

Cheers

• ###### Re: Sum values up to x numbers of months

Or try doing like this in the expression:

```=sum({<Date = {">=\$(=Addmonths(today(), -3))"}>}budget)

```
• ###### Re: Sum values up to x numbers of months

Hi Mindaugas Bacius

Thanks

However, my idea is to sum the first 5 months or x numbers of months of the year, the number of months should be based in another field of the excel sheet that contains the up-to-date date value.

Cheers

• ###### Re: Sum values up to x numbers of months

I think we do get the idea of what might be needed, but there are still gaps related to the "another field" of the excel sheet. Is there only going to be one value or will there be multiple values in it? I guess seeing some sample data may help explain a lot of the doubts we might have and provide you a solution which might work the best for you.

Best,

Sunny

• ###### Re: Sum values up to x numbers of months

Hi Sunny T. Below you will find a sample of the data I am using. Thank again for your help

• ###### Re: Sum values up to x numbers of months

I guess the question is what is your dimension? Where are you trying to do this? Can you provide more details here?

• ###### Re: Sum values up to x numbers of months

Hi Sunny T,

It is very simple, i have a field called "Accountdate" with the each of the months of the year and a field called "Budget", on my Qlik Sense sheet i have a filter pane with the name of the countries (another field) called "Countries".

So what i am trying to achieve in a #1 KPI is to get the sum of the first x months of the budget. in the 1# KPI the only thing i have is the expression as measure: sum({\$<Date={'\$(=max([Accountdate]))'}>} budget)

Cheers

• ###### Re: Sum values up to x numbers of months

I don't know if this works for you, but you could try this for getting the sum of the first three months.The syntax for the date (01-04-2016) may vary per country/excel file. I'm not sure about that.

sum({\$<Date={'<01-04-2016'}>}Budget)

• ###### Re: Sum values up to x numbers of months

Try this:

You have to add a ID field in your table, unique for each row.

sum({\$<ID={"=(Accountdate=MAX(DateRange))"}>}Budget)

DateRange refers to your other table where the latest date is stored.

Greetings,

Timo

• ###### Re: Sum values up to x numbers of months

HI Timo, i have tried but no luck, i did add another field in my table but the results is totally wrong

Thanks

• ###### Re: Sum values up to x numbers of months

Maybe it has to do with the dateformat.

Here you find my QlikSense file. Check my settings in the load script.

Hope it helps.

Greetings,

Timo

• ###### Re: Sum values up to x numbers of months

I have the same date as you are in your example, I managed to get the sum of the values with the same date (in your example 115 corresponds to Mar 2016), but I need to get the sum of values of the previous months as well including the latest.

Cheers

• ###### Re: Sum values up to x numbers of months

You can then use the formula:

sum({\$<ID={"=(Accountdate<=MAX(DateRange))"}>}Budget)

Good to know it works!

• ###### Re: Sum values up to x numbers of months

Hi Time Schrama,

Thanks a lot for your help. it works perfectly!!!@

Thank for taking the time.

Cheers