Valuelist in analysis to determinate a date

[#1]

Hello,

I'm trying to have the performance of a fund for different periode. I've the pivot table below:

Titre	EONIA	BNP Insticash EUR	Date	EONIA at  30/06/2011	top(sum({<Fund_Name={EONIA_CAPI},     DATE_VALEUR={-}>} NAV))
1 Year	-	-	30/06/2010	130,582993	0
3 months	-	-	31/03/2011	130,582993	0
6 months	-	-	30/12/2010	130,582993	0
Year today	-	-	01/01/2011	130,582993	0

My problem is on the column 5. How to determinate the "DATE_VALEUR" within the set analysis function of the valuelist dimension?

here is my code:

top(sum({<Fund_Name={EONIA_CAPI},

top(sum({<Fund_Name={EONIA_CAPI},

DATE_VALEUR={$(=if(Valuelist('1 Year','6 months','3 months','Year today')='1 Year',date(Daystart(vDateRef,-365)),

        if(Valuelist('1 Year','6 months','3 months','Year today')='6 months',date(Daystart(vDateRef,-182)),

        if(Valuelist('1 Year','6 months','3 months','Year today')='3 months',date(Daystart(vDateRef,-91)),

        if(Valuelist('1 Year','6 months','3 months','Year today')='Year today',date(Yearstart(vDateRef))

        )))))}>} NAV))

Where I'm wrong?

The column date returns me the correct date with the formul:

=if(Valuelist('1 Year','6 months','3 months','Year today')='1 Year',date(Daystart(vDateRef,-365)),

        if(Valuelist('1 Year','6 months','3 months','Year today')='6 months',date(Daystart(vDateRef,-182)),

        if(Valuelist('1 Year','6 months','3 months','Year today')='3 months',date(Daystart(vDateRef,-91)),

        if(Valuelist('1 Year','6 months','3 months','Year today')='Year today',date(Yearstart(vDateRef))

        ))))

Thanks for your help

Laurent

[swuehl]

Hi Laurent,

could you describe a bit more detailed what you want to achieve with that calculation?

What do you want to achieve with the inter record top() function?

Furthermore, it might be an idea to replace the magic day numbers for months calculation by using addmonths() function instead.

Regards,

Stefan

[swuehl · Accepted solution]

me again,

(assuming that the there are no other problems) I think that you want DATE_VALEUR to be assigned the value of Date in that specific row.

1) Do you want it to be assigned that value or a range of dates (e.g. today back to that date)?

2) More important, I think that comparison with dimension will not work inside your set expression.

Please have a look at  http://community.qlik.com/docs/DOC-1335

There is also a possible solution discussed, Select several sum expression with set expression using if clauses (i.e. putting the sums inside the if clauses).

Hope that helps,

Stefan

[#4]

Hello Stefan,

Thanks for your help,

My goal was to show the performance of the fund (BNP Insticash EUR) compared with the benchmark EONIA.

This for 1 year until a reference date ("Date Référence" in the pink case), 6 months, 3 months and from the begin of the year until this reference date.

You can find the result in the joined file with the pivot table "Evolution"

Regards

Laurent