how to show only last month data and not the whole year if no month is selected

[chriscools]

Hello,

i have a tabel to compare sales of this month, last month, and this month last year.

but if i don't choose a month, than it gives me the sales for the total year.

how would i have to do it that if no month is selected only last month is chosen?

this is the formula i have

Sum({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}>}LineSalesAmount) 

and i tried this: (in analogy with the max year)

Sum({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))} Month= {$(=Max(Year))}>}LineSalesAmount) 

but then i don't get my calculation at all!

what am i doing wrong?

thanx!

chris

[christophebrault]

Hi,

try this :

Sum({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))} Month= {$(=Max(Month))}>}LineSalesAmount)

Where Month is a field with integer

[#3]

well, first what u're doing wrong in:

Sum({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))} Month= {$(=Max(Year))}>}LineSalesAmount) 

u're equating Month to max(Year),even if u equate Month to some Month then also u need to separate two sets using a comma as u're using between the sets Shop & Year

Now,what u can do is;

if(isnull(getfieldselections(Month)),ur expr for prev month LineSalesAmount,ur current expr)

Hope this helps !!

[chriscools]

Hello,

that was what i treed, i see now that i typed Year instead of Month here...

so i used month before and that gives me a zero.

it still doesn't work...

i calculate also the figures for last month with this formula

Sum({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}, Month=, Monthnum={$(= max (Monthnum)-1)}}>}LineSalesAmount) 

i tried the same like this for this month, removing the "-1".

Sum({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}, Month=, Monthnum={$(= max (Monthnum))}}>}LineSalesAmount) 

for last month that works, that only shows me last month regardles if i have a selection or not , but for this month it 's not ok...

grtz

[#5]

I think

if(getSelectedcount(Month) > 0,

Sum({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}>}LineSalesAmount)  ,

Sum({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}, Month=, Monthnum={$(= max (Monthnum))}}>}LineSalesAmount)  )

will work better I made some bad experience with isnull

regards,

MT

[#6]

go ahead and post what u get

[magavi_framsteg]

Hi ChrisCools!

You can always select a, perhaps, more straight forward and readable approach.

Use one object if a month is selected.

Another object if no month is selected.

This way, your {sets} become easier to read and calculate more efficient.

Again, it's an opinion of taste and preference.

Kind regards

Magnus Åvitsland

BI Architect Consultant

Framsteg Business Intelligence Corp.

[chematos]

Do you have a MonthYear field in your calendar?

if(GetSelectedCount(Month) <= 0,

Sum({<Shop= {'Pomax Store Wijnegem'}, MonthYear={$(= Max(MonthYear))}, Year =>}LineSalesAmount),

Sum({<Shop= {'Pomax Store Wijnegem'},Year = {$(= Max(Year))}>}LineSalesAmount))

Regards

[chriscools]

Hello,

hereby i included my file.

i adapted the formula with an if-clause and the getSelectedCount.

but still nothing.

grtz,

chris

[chematos · Accepted solution]

if( GetSelectedCount (Month) > 0,

Sum({<Shop= {'Pomax Store Wijnegem'}, Year = {$(= Max(Year))}>}LineSalesAmount) ,

Sum({<Shop= {'Pomax Store Wijnegem'},

Year={'=max(Year)'},Month=$(=month(max(Date)))}>}LineSalesAmount))