Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Using a variable for full set analysis string

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.

Tags (1)
1 Solution

Accepted Solutions
sebastiandperei
Valued Contributor

Re: Using a variable for full set analysis string

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 !!


5 Replies
Not applicable

Re: Using a variable for full set analysis string

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

It better to create this expression on the script.

sebastiandperei
Valued Contributor

Re: Using a variable for full set analysis string

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 !!


Not applicable

Re: Using a variable for full set analysis string

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)

)

Not applicable

Re: Using a variable for full set analysis string

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

sebastiandperei
Valued Contributor

Re: Using a variable for full set analysis string

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.

Community Browser