Using a variable for full set analysis string

[#1]

Hi,

I'm building a table that has a lot of set analysis work in it. An example of one of my field calculations below:

=if(vDateType='Service',

  count({$<SERVICEDATE={">=$(=vFromDate) <=$(=vToDate)"},BDT_BOOKINGSTATUS={"CONFIRMED"}>}TOURREF),

  count({$<BOOKINGDATE={">=$(=vFromDate) <=$(=vToDate)"},BDT_BOOKINGSTATUS={"CONFIRMED"}>}TOURREF)

)

(I have two distinct date fields per record - SERVICEDATE and BOOKINGDATE on each record of my fact table. Requirement is to be able to select a From and To date and then identify if this should apply to the SERVICEDATE or BOOKINGDATE field and build various values around that.

I'm going to be reusing this set analysis across multiple objects, so was wondering if there was anyway of capturing all the set elements  ( {$<SERVICEDATE={">=$(=vFromDate) <=$(=vToDate)"},BDT_BOOKINGSTATUS={"CONFIRMED"}>} ) into two variables (one for SERVICEDATE, one for BOOKINGDATE). That way if I ever need to change this in the future I can change in one place to affect multiple charts etc.

Tried a few things, but just can't seem to make this work.

Thanks,

Rory.

[#2]

Hi Webber, Can you please post what ur variable holds the SET expression.

It better to create this expression on the script.

[sebastiandperei · Accepted solution]

Go to Variable Panel (Ctrl + Alt + V)

Puch "Add", and type "vSERVICEDATE". Then, select these variabe, and in the below text box type just:

{$<SERVICEDATE={">=$(=vFromDate) <=$(=vToDate)"},BDT_BOOKINGSTATUS={"CONFIRMED"}>}

Do the same for vBOOKINGDATE.

So, your expression will be:

=if(vDateType='Service',

  count($(vSERVICEDATE) TOURREF),

  count($(vBOOKINGDATE) TOURREF))

I dont recommend, before to use this, study all your expressions, because you will can't modify these Set Analysis for a particular expression. But, if you know that this exp is exacltly the same in much places, it will help !!

[#4]

Hi Dathu,

Variable vConfirmedServiceDateSetCriteria holds the content: {$<SERVICEDATE={">=$(=vFromDate) <=$(=vToDate)"},BDT_BOOKINGSTATUS={"CONFIRMED"}>}

Variable vConfirmedBookingDateSetCriteria holds the content: {$<BOOKINGDATE={">=$(=vFromDate) <=$(=vToDate)"},BDT_BOOKINGSTATUS={"CONFIRMED"}>}

My expression in my chart then reads:

=if(vDateType='Service',

  sum(=$(vConfirmedServiceDateSetCriteria)SALESPRICEEUR),

  sum(=$(vConfirmedBookingDateSetCriteria)SALESPRICEEUR)

)

[#5]

Thank you Sebastian! The extra =  I had in my chart expression was the issue.

[sebastiandperei]

Good!

The "=" is needed if you have an expression and you want to execute it.

So, in this case, you only use the var to replace text, not for execute any expression.