Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Comunity, I have the follow problem:
I Use a Excel File to get contable information from a period, for example:
Month Should Have Balance Currently month Balance Previous Month
1 500,0 300,0 200,0 0
2 800,0 200,0 600,0 200
3 100,0 150,0 - 50,0 600
I need to show this information on a Pivot Table..
When I use a "Normal" Table and get Previous Balance on an external Field with a Set Analisys its Works well.
My set analisys is this:
If(GetSelectedCount(Month)>=1,if(Month=1, 0, sum({<Month1={$(=Max(Month)-1)}>} ([Should]-([Have])))))
But when I used this form in a Pivot Table does not Work.
In this Case I decided use a Before Sentence for get Previous Month . Its works
Before(SUM([Should]-([Have])))
But I have a Big problem... When I make a Filter by dimension for example Month This information disapears.
I didn't Know how can I do for use a Pivot Table With Filters Dimensions and Get Previous Balance.
I Need Help.
Thanks
Try useing this measure:
aggr(above(Sum({<Month>}[Balance Currently month])),Month)
result:
if I select Month 2 for example:
Create this new field in the script :
Capitalize( PurgeChar( month( date#( Month,'M')),'.')) as MonthName
and change ur expression from :
aggr(above(Sum({<Month>}[Balance Currently month])),Month)
to
aggr(above(Sum({<Month,MonthName>}[Balance Currently month])),Month)
Try useing this measure:
aggr(above(Sum({<Month>}[Balance Currently month])),Month)
result:
if I select Month 2 for example:
Thanks is all I need!
Try :
=month( date#( Month,'M'))
And to make it even better :
=Capitalize( Purgechar( month( date#( Month,'M')),'.'))
It's Correct my Friend but, when I proceed to convert and I select for example feb. the field 'Previous Balance' Is get in blank "disappeared". How Another idea?
Create this new field in the script :
Capitalize( PurgeChar( month( date#( Month,'M')),'.')) as MonthName
and change ur expression from :
aggr(above(Sum({<Month>}[Balance Currently month])),Month)
to
aggr(above(Sum({<Month,MonthName>}[Balance Currently month])),Month)
Look the image and you can understand that I say.
Please... read my last answer carefully and u'll correct this; I have to go !