Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wgonzalez
Partner - Creator
Partner - Creator

Summing a value up to Selected Year

Hi,

I've created a Pivot Chart.  I need to sum all values in a field up to selected year for the selected dimension.

For example, if user selects year 2017, the values in field Inception should be summed up for years 2016 and 2017, assuming available data starts in year 2016.  If user selected year 2018, values in field Inception should be summed up starting in 2016, 2017 and 2018; etc.

Sample data in attached file.

 

1 Solution

Accepted Solutions
Kushal_Chawda

Assuming you have Year field available.

Create variable vSelectedYear  and put below in value

=max(Year)

In Pie chart, put expression like below

= sum({<Year ={"<=$(vSelectedYear)"}>}Sales)

View solution in original post

5 Replies
Kushal_Chawda

Assuming you have Year field available.

Create variable vSelectedYear  and put below in value

=max(Year)

In Pie chart, put expression like below

= sum({<Year ={"<=$(vSelectedYear)"}>}Sales)

wgonzalez
Partner - Creator
Partner - Creator
Author

Not working. 😞

I've tried all of these options, but none have worked.

=sum({<FiscalYear={"<=$(vSelectedYear)"}>}Inception)
=sum({<FiscalYear={"<=$(=max(FiscalYear))"}>}Inception)
=Sum({<FiscalYear={">=$(=Min(FiscalYear))<=$(=Max(FiscalYear)+1)"}>}Inception)

Jesh19
Creator II
Creator II

Hi,

Try the below expression.

=Sum({<FiscalYear={">=$(=Min({1}FiscalYear))<=$(=Max(FiscalYear))"}>}Inception)

Thanks,

Jesh

Kushal_Chawda

what is the format of FiscalYear? Make sure it is number format. Try to check max(FiscalYear) value in text object or KPI object(qlik sense)

wgonzalez
Partner - Creator
Partner - Creator
Author

Hi, I was using the year field in the calendar table for the Years List Box.  Now I'm using the Year field in the fact table, which works precisely.

Thanks!