Set analysis with parameter

[#1]

I have this expression in a pivot chart:

Sum({$<Date= {"<$(=Date(ArticleReleaseDate))"}> } Sales)

I want to show how much the article have sold before it was released. However, when i select one article the expression works. But when i dont have anything selected it does not, instead it shows the total sales for each product.

Any tips?

[MayilVahanan]

Hi

     Try like this,

     Sum({<Date= {"<$(=Date(ArticleReleaseDate))"}> } Sales)

[Anonymous]

Have you got an example to try on ?

Jonathan

[#4]

Same problem im afraid. Thanks anyway

[#5]

No I havnt, the application is to big.

[#6]

Hi,

Add the max() function to expression:

Sum({$<Date= {"<$(=Date(Max(ArticleReleaseDate)))"}> } Sales)

Regards,

Ricardo

[jonathandienst · Accepted solution]

Hi

The reason this only works when something is selected is that without a selection, which article should the expression use? Remember set analysis executes outside the context of a chart/table and has no knowledge of the dimensions. If you are using this inside a table and want the date subject to the article (and the article is a dimensionm or unique within a dimension), then the only approach is to use sum(If())

• Sum(If(Date=<Date(ArticleReleaseDate) Sales))

SumIf does perform poorly compared to set analysis, and if that approach takes too long to compute, you might want to add a flag in the load script to indicate sales with dates less than the release date. Then you can use something like:

• Sum({<ReleaseFlag={1}>} Sales)

Hope that helps

Jonathan

[jonathandienst]

The load script could look something like:

Join (Sales)

LOAD ArticleNr,

     ArticleReleaseDate

Resident Articles;

Join (Sales)

LOAD Date,

     ArticleNr,

     If(Date < ArticleReleaseDate, 1, 0) As ReleaseFlag

Resident Sales;

DROP Field ArticleReleaseDate From Sales;

Adjust the script accordingly for your object names. I am assuming that you have a ArticleNr exists in both tables.

Regards

Jonathan

[#9]

Thanks Jonathan. I will try this.