Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
GrantBooth
Contributor III
Contributor III

Sum YTD

I'm trying to create a cumulative sum for YTD based on a user's selection

The user selects PeriodYear (an integer value), PeriodMonth (integer from 1-12), so I would like the sum(Amount) from month 1 to the users selection. I've been trying solutions suggested online and I simply can't get this to work, any help would be really appreciated.

6 Replies
Vegar
MVP
MVP

If user selects both PeriodMonth and PeriodYear then this should be sufficient. 

SUM({<PeriodMonth={"<=$(=max(PeriodMonth))"}>} Amount)

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
GrantBooth
Contributor III
Contributor III
Author

Thanks for that. When I do this, it gives only the value of the selected month and not the cumulative amount for the year

GrantBooth_2-1632460655269.png

 

The above is if month 1 is selected

GrantBooth_3-1632460708885.png

 

If Month two is selected

And if month 1 and 2 selected

GrantBooth_4-1632460735519.png

 

What I would like to see is if a month is selected, Budget column shows only that month, and BudgetYTD shows the sum of each month from the beginning of the year.

 

 

GrantBooth
Contributor III
Contributor III
Author

Correction, if month 1 and 2 are selected

GrantBooth_5-1632460928262.png

 

Vegar
MVP
MVP

Do you have other current selection  than the PeriodMonth selections? How does it behave if you only make selections in that field?

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
GrantBooth
Contributor III
Contributor III
Author

There is another field selected. Because of another issue with the data set, I have had to create a filter to only show data relating to the latest load date, called Calendar 1. I'm just doing some testing, please bare with me

GrantBooth
Contributor III
Contributor III
Author

I have removed that value and still have the same problem. For reference, expression used for Budget is

fabs(Sum({ <ProductType = {'Budget'}>} Amount))/1000, and the expression used for BudgetYTD is 

SUM({<PeriodMonth={"<=$(=max(PeriodMonth))"}> * <ProductType = {'Budget'}>} Amount)/1000

If month 1 is selected I get

GrantBooth_3-1632463225730.png

 

If I select month 2 i get

GrantBooth_4-1632463259585.pngAnd if both are selected I get

GrantBooth_5-1632463285140.png