Hi All
Hopefully i can explain this well enough.
i have a line chart that has a dimension of YearWeek and the measures are counting a field called MATCH based on a refined criteria. see below (Measures Script) for the script. As part of this page i also have 2 variable buttons and a variable input box. The input box allows the client to input the YearWeek (201901 is week one of 2019) they want to restrict the data to. this restricts KPIs to that weeks data and charts for that year up to this week. As far as the chart is concerned if the input date is before 2019 then the chart will show data from week one of 2018 up to the input date (vWeek). The same will happen for a 2019 date i.e. the chart will be restricted to >= 201901 to the input week (vWeek).
The Variable buttons (vTime) restrict the charts to a YTD or the last 12 weeks, again both based on the input of the input variable (vWeek).
what i now want to do is to include nulls but again only based on a set criteria. so, if a 2019 date (vWeek = 201919 (Week 19 of 2019)) is input then i want the data to show all weeks from the start of that year (2019) up to and including 201919 including any null values. if i currently just include nulls in Add ons > Data Handling > Include Zero Value then i see all YEARWEEKS regardless of the year but restricted to the input date. I assume i need to put something in to the express in Data Handling but i'm at a loss as to what this should be.
Measures Script
if(vTime=1,
if(vWeek<201901,
Count({$< MATCH ={'BV Not AV','BV not AV'},YEARWEEK={">=$(=201801)<=$(vWeek)"}, ESCALATE={'Y'}>} MATCH),
Count({$< MATCH ={'BV Not AV','BV not AV'},YEARWEEK={">=$(=201901)<=$(vWeek)"}, ESCALATE={'Y'}>} MATCH)),
if(vWeek <201901,
Count({$<MATCH ={'BV Not AV','BV not AV'}, YEARWEEK = {">=$(=vWeek-11)<=$(=vWeek-0)"},ESCALATE={'Y'} >}MATCH),
Count({$<MATCH ={'BV Not AV','BV not AV'}, YEARWEEK = {">=$(=vWeek-11)<=$(=vWeek-0)"}, ESCALATE={'Y'} >}MATCH)))
