Hi All,
I have set of data containing the following dimensions: CustomerNames, StartYear, StartQuarter, EndYear, EndQuarter which tells me when cases have been opened and closed for these customers. I want to create a report that for any given time (two input fields/variables as "SelectedYear and SelectedQuarter), it will tell me how many "OPEN" cases there were. An "OPEN" case is defined as a count of customer for which the StartYear/Quarter is less than the SelectedYear/Quarter but the EndYear/Quarter is greater than Selected Year/Quarter. My Expression is as follows:
count(if(StartYear&StartQuarter <= SelectedYear & SelectedQuarter and EndYear&EndQuarter >= SelectedYear & SelectedQuarter, CustomerNames))
This gives me the correct results. However, it will only give me one bar in the chart which shows the result for my two variable input, i.e. 2015 2. I want to show the selected variable as well as 4 previous quarters. How do I do this?
Note I don't have any dates or a master calendar or quarter mapping. One problem that I've encountered is qlikview doesn't recognize year quarter sequence - for instance if I try and use SelectedYear&SelectedQuarter - 3, and I've chosen 2015 3, it will result in 2015 0 instead of 2014 4.
Appreciate any help.
Thanks,
Carlo
