Hi QlikCommunity folks,
I am creating a total sum table by using Set Analysis, for the first 2 year data, I want to sum the total 1st 24 months (i.e. year 1 and year 2 total) value and starting from the 3rd year, the value is based on the previous 2 year data (i.e. year 3 will show the total of year 1 and year 2, and year 4 will show the total of year 2 and year 3). For each product, I have individual tab to provide detailed analysis pertains to that product only (by having a trigger when activating the tab associated with that product with a filter type of product = XXXX).
I was using the following expression to calculate the total sum for the first 2 years:
if
([Event Year IMDt]<=2011, sum({$<[Event Year IMDt]={2010,2011} ,[Event Month IMDt]=, [Event Month Name IMDt]= Isolate.ProductCode={'0001','0002','0003'},[Type of Products]=>} total aggr(count({$<[Event Year IMDt]={2010,2011} ,[Event Month IMDt]=, [Event Month Name IMDt]= ,Isolate.ProductCode={'0001','0002','0003'},[Type of Products]=>}SurveillanceFile.PK),[Event Month Name IMDt])), null())
However, not all products have sales for each month, and when I used the above expression, the months that had no sales will show up as with NULL vs. the total sales. For example, is May 2011 does not have sale for product X, then the total sum column will show a "-" even though it meets the if statement criteria
Does anyone have idea on how to tackle this?
Thank you so much!
