
1. Re: Data aggregation and conditional sums
Simon Runc Jul 26, 2017 6:42 AM (in response to Timothy Dalton)hi Timothy,
So one way is to use an LoD to create a dimension of "numbers of years sold" like so
{FIXED [Product]: COUNTD([Year])}
You can then use this in the filter, or in calculations.
Hope that helps

2. Re: Data aggregation and conditional sums
Timothy Dalton Jul 26, 2017 6:57 AM (in response to Simon Runc)Hi Simon,
Thanks a lot for the response!
But, I also have 0 or Null values for years with no sales for one product.
So also rows like
120 2015 0
How do I update it then?
Thanks again!!

3. Re: Data aggregation and conditional sums
nawazuddin mohammed Jul 26, 2017 6:59 AM (in response to Timothy Dalton)1 of 1 people found this helpfulI agree with the Simon's logic. Another way is to define a calculated field in the set and use it in the filter.
Thanks,
Nawaz

4. Re: Data aggregation and conditional sums
Simon Runc Jul 26, 2017 7:01 AM (in response to Timothy Dalton)1 of 1 people found this helpfulhi Timothy,
So we can make a small tweak to our formula....
{FIXED [Product]: COUNTD(IIF(zn([volume])>0,[Year],NULL))}
As NULLs don't get counted in COUNTD it will only count the years with sales.
You may need to check the number of brackets as I've not used the editor to check this formula (but the logic should work)

5. Re: Data aggregation and conditional sums
Timothy Dalton Jul 26, 2017 7:12 AM (in response to Simon Runc)This is super helpful, thanks a lot Simon!

6. Re: Data aggregation and conditional sums
Timothy Dalton Jul 26, 2017 7:13 AM (in response to nawazuddin mohammed)Thanks a lot Nawazuddin! I finally grasped the logic of formulas in sets