Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Please check enclosed file...
Maybe like
=sum({< [ACTUAL SALES] = {">0"}>} [PLAN])
Hi,
you can write the following expression:
Sum( {<Date_Field = {"=not isnull(Actual Sales)"} >} PLANS)
-Nilesh
Try this
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
=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.
i have attached the file
Please check enclosed file...
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
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...