topic Re: Get Year-To-Date values in QlikView

Get Year-To-Date values

HermGerm — Wed, 25 Nov 2020 16:16:04 GMT

if one selects a year and a month, I want to show year to date values.

Example:

Chosing: 2018 and Mar

Should calculate everything from the beginning of that year (2018) till the end of selected month (March)

Im using this, but its not working

sum(
{<
    DocType={'R','G'}, 
    MainDate={">=$(=YearStart(sh_ActDatSelectedYear))<=$(MonthEnd('01.'&max(MainDateMonth)&'.'&max(MainDateYear)))"}
>}
Turnover_Factura
)

It is just showing the values of the selected period (only march 2018), but not the whole year until end of march

sh_ActDatSelectedYear contains in that example '11.01.2018'

Re: Get Year-To-Date values

sunny_talwar — Fri, 11 Jan 2019 13:03:54 GMT

Not entirely sure what else might need to change, but you def. need an equal sign for the second part of your set modifier for MainDate

sum(
{<
    DocType={'R','G'}, 
    MainDate={">=$(=YearStart(sh_ActDatSelectedYear))<=$(=MonthEnd('01.'&max(MainDateMonth)&'.'&max(MainDateYear)))"}
>}
Turnover_Factura
)

Re: Get Year-To-Date values

HermGerm — Fri, 11 Jan 2019 14:35:17 GMT

Iguess the problem is that the selection is on 2018 and march, not jan and feb, but i want to include those into my calculation, how do i do this. But not sure tho

Re: Get Year-To-Date values

sunny_talwar — Fri, 11 Jan 2019 14:53:46 GMT

I guess ignore selection in the Month field?

sum(
{<
    DocType={'R','G'}, 
    MainDate={">=$(=YearStart(sh_ActDatSelectedYear))<=$(=MonthEnd('01.'&max(MainDateMonth)&'.'&max(MainDateYear)))"},
    MonthField
>}
Turnover_Factura
)

Re: Get Year-To-Date values

Donicc — Fri, 11 Jan 2019 15:31:53 GMT

Instead of specifying a range in the set, eg, Jan 1 < TargetDate < Selected Date, I find it easier to simply specify the year and the month separately.

Below is an example of prior year to date. CMOYR_YR and MonthYear are fields from the common calendar. vPriorYear, vPriorMonthYear, vMaxYear, and vMaxMonthYear are variables calculated from the current selection.

Current Year to selected date:

sum({<CMOYR_YR={'$(vMaxYear)'}, MonthYear={"<=$(vMaxMonthYear)"}>} AIMS.PSI_FER_FLAG_ICCA)

Prior Year to selected date:

sum({<CMOYR_YR={'$(vPriorYear)'}, MonthYear={"<=$(vPriorMonthYear)"}>} AIMS.PSI_FER_FLAG_ICCA)