11 Replies Latest reply: Jan 25, 2014 12:26 PM by Manish Kachhia

# Struggling with Set Analysis

Hello,

I am struggling with something and would need help.

I have a table with shows PLAN and ACTUAL SALES for Financial Year 2014.(which starts from Sept 2013)

Now when I click on year it sums sales (i.e. 8) and sums plan (i.e. 43) .... but I want to find a way so that it sums plan only for the month where we have a sales numbers i.e. it should only sum plan = 2+3+4+3 ....

Not sure how to find out the month which have actual sales.

 Month '14 Plan(\$) ' 14 Actual Sales(\$) '14 Sep 2 2 Oct 3 3 Nov 4 2 Dec 3 1 Jan 2 Feb 3 Mar 4 Apr 5 May 6 Jun 4 Jul 3 Aug 4 Total 43 8

H

• ###### Re: Struggling with Set Analysis

Maybe like

=sum({< [ACTUAL SALES] = {">0"}>} [PLAN])

• ###### Re: Struggling with Set Analysis

Hi,

you can write the following expression:

Sum(  {<Date_Field = {"=not isnull(Actual Sales)"} >}   PLANS)

-Nilesh

• ###### Re: Struggling with Set Analysis

Try this

• ###### Re: Struggling with Set Analysis

Thanks guys,

Sales have negative values ... so >0 logic not returning correct answer

Nilesh,

Your logic, returns 0, so it is not summing up anything

• ###### Re: Struggling with Set Analysis

=sum({\$<[Sales Actual]={"=not isnull(Actual Sales)"}>}[Plan Value])

This is what I have tried

I also added empty rows in sales for the remaining months.

• ###### Re: Struggling with Set Analysis

i have attached the file

• ###### Re: Struggling with Set Analysis

Thanks Manish.

It works.

Will be even great  if you can explain the dimension you have added

=IF(AGGR(SUM([Sales Actual]),Month)>0,Month)

thanks buddy!

H

• ###### Re: Struggling with Set Analysis

AGGR(SUM([Sales Actual]),Month

This will give you SUM([Sales Actual]) group by Month

IF(AGGR(SUM([Sales Actual]),Month)>0..... will check if SUM([Sales Actual ]) greater than 0 then only it will take

Month as Dimension...

• ###### Re: Struggling with Set Analysis

Manish,

Can we somehow replace >0 by isnotnull or something?

As sales can be -ve

• ###### Re: Struggling with Set Analysis

Haven't tried but may be something like below...

=IF(AGGR(SUM([Sales Actual]),Month)>0 and Not ISNULL(AGGR(SUM([Sales Actual]),Month)),Month)