Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ruthubarra
New Contributor II

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,

Please

1 Solution

Accepted Solutions
timosdna
New Contributor III

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!

16 Replies
MVP
MVP

Re: Sum values up to x numbers of months

You can try chart expression property for that, like:

Untitled.png

ruthubarra
New Contributor II

Re: Sum values up to x numbers of months

Thank you tresesco, but i am using Qliksense

Cheers

MindaugasBacius
Valued Contributor II

Re: Sum values up to x numbers of months

Or try doing like this in the expression:

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

ruthubarra
New Contributor II

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 guess the question is what is your dimension? Where are you trying to do this? Can you provide more details here?

ruthubarra
New Contributor II

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

timosdna
New Contributor III

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

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

ruthubarra
New Contributor II

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

Budgetsample.png

Community Browser