topic Re: Using a variable for full set analysis string in QlikView

Using a variable for full set analysis string

Fri, 25 Jul 2014 16:21:55 GMT

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.

Re: Using a variable for full set analysis string

Fri, 25 Jul 2014 16:30:28 GMT

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

It better to create this expression on the script.

Re: Using a variable for full set analysis string

sebastiandperei — Fri, 25 Jul 2014 16:33:33 GMT

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

Re: Using a variable for full set analysis string

Fri, 25 Jul 2014 16:34:27 GMT

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)

)

Re: Using a variable for full set analysis string

Fri, 25 Jul 2014 16:35:54 GMT

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

Re: Using a variable for full set analysis string

sebastiandperei — Fri, 25 Jul 2014 17:28:31 GMT

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.