Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The following code used to provide Sales total for the current YTD.
sum( { $ <Year= {$(=max(Year))},PeriodDate= {"<=$(today()-1)"}> } Sales)
It stopped working once we upgraded to Qlikview May 2021
The only thing that worked was to modify the set analysis to use an intersection of two set identifiers instead of one
sum( { $ <Year= {$(=max(Year))}>*<PeriodDate= {"<=$(today()-1)"}> } Sales)
Would anyone know why the two formulas above act differently?
Just use below
Current year YTD
=sum( { $ <PeriodDate= {">=$(=Date(yearstart(Max(PeriodDate))))<=$(=Date(Max(PeriodDate)))"}> } Sales)
Previous year YTD
=sum( { $ <PeriodDate= {">=$(=Date(yearstart(Max(PeriodDate),-1)))<=$(=Date(Addyears(Max(PeriodDate),-1)))"}> } Sales)
In regards to your expressions
Today()-1. Will return a number, it needs be formatted as date
$(=Date(today()-1))
Your second expression is also actually not working, but just seems like it's working
You may already know * returns intersection of sets
<Year= {$(=max(Year))}> //returns all data for max year
* << Interesect
<PeriodDate= {"<=$(today()-1)"}> // this set is actually returning everything because its comparing date with numeric value
So actually the intersection and year =max(year) is the only thing working