topic Re: Set analysis with parameter in QlikView

Set analysis with parameter

Thu, 13 Sep 2012 14:06:17 GMT

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?

Re: Set analysis with parameter

MayilVahanan — Thu, 13 Sep 2012 14:09:23 GMT

Try like this,

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

Re: Set analysis with parameter

Anonymous — Thu, 13 Sep 2012 14:12:45 GMT

Have you got an example to try on ?

Jonathan

Re: Set analysis with parameter

Thu, 13 Sep 2012 14:20:13 GMT

Same problem im afraid. Thanks anyway

Re: Set analysis with parameter

Thu, 13 Sep 2012 14:21:00 GMT

No I havnt, the application is to big.

Re: Set analysis with parameter

Thu, 13 Sep 2012 14:32:00 GMT

Hi,

Add the max() function to expression:

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

Regards,

Ricardo

Re: Set analysis with parameter

jonathandienst — Thu, 13 Sep 2012 14:32:57 GMT

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

Re: Set analysis with parameter

jonathandienst — Thu, 13 Sep 2012 14:35:52 GMT

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

Re: Set analysis with parameter

Thu, 13 Sep 2012 14:39:19 GMT

Thanks Jonathan. I will try this.