TimePeriods as Dimetions

[xtrimf]

Hi,

my customer requires a report that in one column shows the sales from January till current month-1,

in the second column - the sales for the current month.

Class() is not working properly in this case...

I also managed to create a calculation that does "January-May" & "Jun" (as two columns, for example), but the numbers are wrong...

Any suggestions?

Tnx

[#2]

What does your data look like?

If you have months as a dimension in your data, you could do something like:

Exp 1: Sum({<Month = {'>=1<$(=Month(Today()))'}>} Sales)
Exp 2: Sum({<Month = {'$(=Month(Today())'}>} Sales)

If you have sales data and only have the sale date, I would suggest adding month in the load script. You could do something similar to the above using straight dates, but it's a bit more complicated.

[xtrimf]

hi,

I can't use expressions because I'm already using them as dimentions to populate the table.

I need a calculated dimention.

And Yes, I am using "Month".

[xtrimf]

?

[xtrimf]

attached is an illustration of what i'm trying to achieve

[#6]

Calculated dimensions can be a little tricky. You can't seem to use Aggreagation functions inside, trying to calculate your date span in the calculated dimension is out.

I did a Resident Load on the data and pulled out the min and max of the span. Then I built a calculated dimension to either be the current month or anything else, which then displays the span. The calculated dimension is messy, due to trying to pull out the month names using the number. Essentially, it is:

if(Month = Month(Today()), Month, SpanName)

I've attached a sample app that contains the resident load and the calculated dimension, along with a pivot table resembling your example.

[xtrimf]

Its all good...but the dimention should be dynamic and not static....

[prieper · Accepted solution]

Hi,

it should be possible to solve it also by SET-functions, but am not that firm. You may determine your timespan by some variable, which then should be hidden somewhere is your application.

See the attached example

HTH
Peter

[xtrimf]

Tnx 🙂

Works great...i did something similar but your is more accurate 🙂

tnx again