ApplySimple Problem in the creation of filters!!! HELP!!! 1

[AnaFlor]

Hello,

I was trying to create a filter with a custom qualification in the ID of the attribute month like this:

ApplySimple("datepart(year,dateadd(month,-1,getdate()))*100+datepart(month,dateadd(month,-1,getdate()))",0)

When I validate these it gives me an error that shows the following:
You must complete the qualification before closing. Please enter a valid expression. Invalid sintax of date/time.

Can you help me please?
Thanks

 

[dmcmunn]

Need a bit more info...

ApplySimple("datepart(year, dateadd(month, -1, getdate()))*100+datepart(month,dateadd(month,-1,getdate()))",0)

doesn't compute...to Microtragedy's parser...

For a filter, try the ApplyComparison() function to apply the SQL logical comparison operator of your choice (example below is equality) using something like this...

ApplyComparison("datepart(year, dateadd(month, -1, getdate()))*100+datepart(month,dateadd(month,-1,getdate())) = #0",[MONTH_ATTRIBUTE_NAME_GOES_HERE]@ID)


Best Regards,
dmcmunn

 

[AnaFlor]

And what if instead of the previous month I want to do the previous week?
The format of the week_id is like this:200352

If I do this:
ApplyComparison("datepart(year, dateadd(month, -1, getdate()))*100+datepart(week,dateadd(week,-1,getdate())) = #0",[WEEK_ATTRIBUTE_NAME_GOES_HERE]@ID)

it doesn't work

Thanks

 

[entaroadun]

ApplyComparison("datepart(year, dateadd(week, -1, getdate()))*100+datepart(week,dateadd(week,-1,getdate())) = #0",[WEEK_ATTRIBUTE_NAME_GOES_HERE]@ID)

The MSTR syntax looks fine...

 

[AnaFlor]

And what if my week is defined not with the getdate but if I have in my week_column the week and it is defined since wednesday to tuesday?

Thanks

 

[entaroadun]

Use an offset value:

ApplyComparison("datepart(year, dateadd(week, -1, dateadd(day,3,getdate())))*100+datepart(week,dateadd(week,-1,dateadd(day,3,getdate()))) = #0",[WEEK_ATTRIBUTE_NAME_GOES_HERE]@ID)

This will adjust day one of the week from Sunday to Wednesday. If 3 doesn't work, try to find an offset that will.

 

[AnaFlor]

OK, Thanks. It works.

Now I have another problem.
Imagine now this:

I have a report with a metric number of customers and with the attribute month.

I want to create a filter that if I am in a month between January and June the report returns for the previous year the last 6 months. If I am in a month between July and December it returns me the previous 6 months of the same year.

My column of year is like this(2004), month(200403) and semester(1S).

Can you help me?

Thanks

 

[chael]

Sounds like you need to model in a transformation table.

These are fairly simple tables (2 columns) that map one date to a range of other dates.

The transformation needs to be modeled in.

TN4100-7XX-0190 is a good place to start...

Entaroadun can probably give the syntax for an Applysimple statement that could do the same thing...the transformation is simply more reusable.

Chael

 

[AnaFlor]

Thank you chae, but I was trying to do this withouth any transformation.

Entaroadun can you show me the syntax to do this?

Thanks

 

[entaroadun]

OK, but only cuz I like you:

ApplyComparison("(case when (datepart(month,getdate()) between 1 and 6) then (#0 between (datepart(year,dateadd(year,-1,getdate()))*100+7) and (datepart(year,dateadd(year,-1,getdate()))*100+12)) else (#0 between (datepart(year,getdate())*100+1) and (datepart(year,getdate())*100+12)) end)",[MONTH_ATTRIBUTE]@ID)

 

[talenx]

entaroadun,

sorry to intrusion.

but I am having a similar problem, I have a report that uses fiscal period that I need to break down into the weeks that are contain in it.
example

period 1 would have normally 4 weeks but some years it will have 5.

week 1 | week 2 | week 3 | week 4 | week 5

I have created a transformation tables for the following:
day
fiscal week
fiscal period
fiscal year

though I have been able to use it I have implemented a column for week of period: it's a column with the values of : 1,2,3,4,5 .
the weird thing is I can write a SQL query using the week of period values and return the correct dataset.

I have all of my WTD,PTD,YTD transformations working correctly but I haven't found a way to apply the above weeks to a single metric within a report that has other metric's in it.

I can see that you tend to understand the Apply* functions within MSTR if is not too much trouble may I ask for some assistance.
thanks
talenx

 

[AnaFlor]

Entaroadun,

Thanks for your help but I still have a problem.

He gives me an error in this part:
then (#0 between (datepart(year,dateadd(year,-1,getdate()))*100+7) and (datepart(year,dateadd(year,-1,getdate()))*100+12))

I think it's because a result of a case can't be between, it must be equal.

Can you help me once again?

Thanks

 

[entaroadun]

talenx,

Could you please elaborate on exactly what you mean by:

"I have all of my WTD,PTD,YTD transformations working correctly but I haven't found a way to apply the above weeks to a single metric within a report that has other metric's in it.

 

[entaroadun]

then ((datepart(year,dateadd(year,-1,getdate()))*100+7)<=#0 and (datepart(year,dateadd(year,-1,getdate()))*100+12)>=#0)

 

[AnaFlor]

entaroadun,

it still doesn't work because it has an error in the <=
and I cannot have two conditions A and B

is it another way to solve this?

Thanks

 

[entaroadun]

Which RDBMS are you using?

 

[AnaFlor]

SQL server 2000

 

[entaroadun]

OK. SQL Server doesn't allow CASE statements in the WHERE clause. Try this:

ApplyComparison("(((datepart(month,getdate()) between 1 and 6) and (#0 between (datepart(year,dateadd(year,-1,getdate()))*100+7) and (datepart(year,dateadd(year,-1,getdate()))*100+12))) or ((datepart(month,getdate()) between 7 and 12) and (#0 between (datepart(year,getdate())*100+1) and (datepart(year,getdate())*100+6))))",[MONTH_ATTRIBUTE]@ID)

Make sure you have the right # of parentheses:

(
(
(
datepart(month,getdate()) between 1 and 6
)
and
(
#0 between
(datepart(year,dateadd(year,-1,getdate()))*100+7)
and
(datepart(year,dateadd(year,-1,getdate()))*100+12)
)
)
or
(
(
datepart(month,getdate()) between 7 and 12
)
and
(
#0 between
(datepart(year,getdate())*100+1)
and
(datepart(year,getdate())*100+6)
)
)
)

 

[chael]

great solution entaroadun...I really hope AnaFlor appreciates your time and help.

Chael

 

[AnaFlor]

OK, it worked perfectly. Now it is returning the months between july and december of the last year.

you helped me a lot

thank you