6 Replies Latest reply on Jul 26, 2017 7:13 AM by Timothy Dalton

# Data aggregation and conditional sums

Hi,

I'm working with sales data and trying to do an analysis for which I am only planning to take into account existing products.

I could not figure out how to implement such conditionality - using calculated fields or window sums etc.

Data structure is as follows

Product ID     Year     Volume

100                 2015     1

120                 2014     3

120                 2015     2

120                 2016     6

130                 2014     3

130                 2015     3

I only want the sum of volume for products which have volumes in every year between 2014-2015-2016, so I need to get the 3+2+6 and Product 100 listed here.

How do I go about it?

Any help is greatly appreciated, thanks a ton!

• ###### 1. Re: Data aggregation and conditional sums

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

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

I 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

1 of 1 people found this helpful
• ###### 4. Re: Data aggregation and conditional sums

hi 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)

1 of 1 people found this helpful
• ###### 5. Re: Data aggregation and conditional sums

This is super helpful, thanks a lot Simon!

• ###### 6. Re: Data aggregation and conditional sums

Thanks a lot Nawazuddin! I finally grasped the logic of formulas in sets