Struggling with Set Analysis

[#1]

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. 😎 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

thanks in advance

H

[swuehl]

Maybe like

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

[nilesh_gangurde]

Hi,

you can write the following expression:

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

-Nilesh

[rustyfishbones]

Try this

[#5]

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

[#6]

=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.

[#7]

i have attached the file

[MK_QSL · Accepted solution]

Please check enclosed file...

[#9]

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

[MK_QSL]

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...