I thought I resolved it, but the sample I gave you has both Sale Year and Year in the same table, but in my original data set they are in different tables. So I created a variable. So in the sample data the dimensions were Year, Sale Year
which was
If(Year([Sale Date]) >= Year, '<1 Year',
If(Year([Sale Date]) >= ((Year)-2) and Year([Sale Date]) < Year,'Prior 2 Years',
If(Year([Sale Date]) <= ((Year)-3),'< 3 Years'))) as [Sales Year] in the script
In my original data set now, I put all the above in a variable and used that variable as a dimension for [Sale Year]
vSaleYear
If(Year([Sale Date]) >= Year, '<1 Year',
If(Year([Sale Date]) >= ((Year)-2) and Year([Sale Date]) < Year,'Prior 2 Years',
If(Year([Sale Date]) <= ((Year)-3),'< 3 Years'))) and used that in my Bar Chart but I get 100% now for each Year because the values Sum(Amount) and Sum(Total <Year> Amount) are the same.
The same logic works perfectly fine, if I put that logic in the script like we did earlier. See the attached sample and qvw file. WIth two charts, one with variable and other without the variable
Am I missing something here?
