Display data in a specific range of dates

[JackRen3]

Hello I'm trying to display, in a barchart, values between the start of the year (which is selected in the filter) up to the month for that same year (which is also selected with the filter). I would like to represent the "year to date" values for the current year and also for the previous year, like this:

In the picture above are represented the "year to date" values of the selected year (2022) up to the month of the selected month (April). The same barchart is shown for the previous year.

I wrote this working expression to show only the data for the selected month and year...how do I implement in the expression the filter to consider the date range between the beginning of the year and the final date? The first expression below is to show data for the year selected with the filter, the second one is for the previous year

• (sum({$<year={$(=GetFieldSelections(year))}>} sales_units) / sum({$<year={$(=GetFieldSelections(year))}>} total <month> sales_units))
• (sum({$<year={$(=GetFieldSelections(year)-1)}>} sales_units) / sum({$<year={$(=GetFieldSelections(year)-1)}>} total <month> sales_units))

As you can see the expression computes the ratio between the sales_units (of the selected month and year) and the sales_units (of all the months). 

Is there a way to write the expression and define that it should take into account only the range between the start of the year up to the the month selected in the filter? 

As right now I have two separate barcharts, one for the selected year and one for the previous year.

[AronC]

@JackRen3 

To begin with I always use a Ids in my calendar-table backend, which I would say is best practice. Good book to read is Qlikview for developers.

Ex:

Load *, AutoNumber(Period, 'PeriodId') as PeriodId;

where Period is defined as Year(Temp_Date)*100 + Month(Temp_Date)

Then in my YTD set expression I use 
sum({$<Year={$(=max(Year))}, PeriodId={"<=$(=max(PeriodId))"}, Month,Quarter>} sales_units)

Then in my YTD for last year set expression I use 
sum({$<Year={$(=max(Year)-1)}, PeriodId={"<=$(=max(PeriodId)-12)"}, Month,Quarter>} sales_units)

[vinieme12]

filter based on dates  instead of year- / month

Current Year YTD

=sum({<datefield={">=$(=Date(YearStart(Max(datefield))))<=$(=Date(Max(datefield)))"}>} sales_units)

Previous Year YTD

=sum({<datefield={">=$(=Date(YearStart(Max(datefield),-1)))<=$(=Date(AddYears(Max(datefield),-1)))"}>} sales_units)

[JackRen3]

how do i implement the datefield in my expression? The datefield is working accordingly with my filters?

These are my basic expressions, in which I want to implement the YTD:

• (sum({$<year={$(=GetFieldSelections(year))}>} sales_units) / sum({$<year={$(=GetFieldSelections(year))}>} total <month> sales_units))
• (sum({$<year={$(=GetFieldSelections(year)-1)}>} sales_units) / sum({$<year={$(=GetFieldSelections(year)-1)}>} total <month> sales_units))

[vinieme12]

do you have a date field in your datamodel?

replace

year={$(=GetFieldSelections(year))}

with

datefield={">=$(=Date(YearStart(Max(datefield))))<=$(=Date(Max(datefield)))"}

and

replace

year={$(=GetFieldSelections(year)-1)}

with

datefield={">=$(=Date(YearStart(Max(datefield),-1)))<=$(=Date(AddYears(Max(datefield),-1)))"}

[JackRen3]

Thank you for your reply.

For the intent of my project I need to keep the "Get Field Selections", since I need to read both the year and the month.

Thanks to your suggestion, I was able to make it work for the "current year" with the following expression:

(sum({<yearmonth_ytd={">=$(=GetFieldSelections(year) & '01')<=$(=GetFieldSelections(year) & GetFieldSelections(month))"}>} sales_units) /
sum({<yearmonth_ytd={">=$(=GetFieldSelections(year) & '01')<=$(=GetFieldSelections(year) & GetFieldSelections(month))"}>} total <month> sales_units))

I'm having troubles to make it work for the "previous year" with the following expression:

(sum({<yearmonth_ytd={">=$(=GetFieldSelections(year)-1 & '01')<=$(=GetFieldSelections(year)-1 & GetFieldSelections(month))"}>} sales_units) /
sum({<yearmonth_ytd={">=$(=GetFieldSelections(year)-1 & '01')<=$(=GetFieldSelections(year)-1 & GetFieldSelections(month))"}>} total <month> sales_units))

[vinieme12]

you don't need getfieldselections() if your data is associated correctly in your datamodel

is the calendar Year field disconnected from the dataset?

can you post a sample app with dummy data?