Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
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.

1 Solution

Accepted Solutions
sebastiandperei
Specialist
Specialist

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


View solution in original post

5 Replies
Not applicable
Author

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

It better to create this expression on the script.

sebastiandperei
Specialist
Specialist

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


View solution in original post

Not applicable
Author

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
Author

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

sebastiandperei
Specialist
Specialist

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.