topic Re: Help with Set Analysis Expression in QlikView

Help with Set Analysis Expression

Tue, 09 Feb 2016 12:08:02 GMT

Hi I'm wondering if anyone can help me with a set analysis problem.

I have a list box with a date field displaying the 1st day of the month for the last year in the format 01/01/2016 for eg.

I have two variables - vDateSel = currently selected date

vDateSel1= the 1st date of the month which follows the currently selected date.

I was looking for an expression which would sum the field Tarv, for type = 'new' or type='upgrade' and for estimated_delivery_date is between vDateSel and vDateSel1. But also ignores the current selection only in the 'date' field.

Can anyone help ? I think I almost have it with this expression but its not quite right.

=Money(Sum({1-$<estimated_delivery_date={">=$(vDateSel)<=$(vDateSel1)"},tl_type={'Upgrade'},tl_type={'New Business'}>}tarv),'#,##0;(#,##0)')

Re: Help with Set Analysis Expression

sunny_talwar — Tue, 09 Feb 2016 13:48:17 GMT

Have you tried this?

Sum({<estimated_delivery_date={">=$(vDateSel)<=$(vDateSel1)"}, tl_type={'Upgrade'}, tl_type={'New Business'}>}tarv)

if date is another field which you want to ignore, then may be this:

Sum({<estimated_delivery_date={">=$(vDateSel)<=$(vDateSel1)"}, tl_type={'Upgrade'}, tl_type={'New Business'}, date = >}tarv)

Re: Help with Set Analysis Expression

Tue, 09 Feb 2016 13:52:14 GMT

You need convert the variable value into your date format. Assume estimated_delivary_time has YYYY-MM-DD format so your variable must return date value in the same format not numeric value. So while defination add the Date function to convert to date format.

=Money(Sum({1-$<estimated_delivery_date={">=$(=Date('$(vDateSel)','YYYY-MM-DD'))<=$(=Date('$(vDateSel1)','YYYY-MM-DD'))"},tl_type={'Upgrade'},tl_type={'New Business'}>}tarv),'#,##0;(#,##0)')

Re: Help with Set Analysis Expression

jonathandienst — Tue, 09 Feb 2016 13:55:37 GMT

Like this perhaps:

=Sum({$<estimated_delivery_date = {">=$(vDateSel) <=$(vDateSel1)"}, tl_type={'Upgrade'}, tl_type={'New Business'}, date>} tarv)

But this assumes that vDateSel and vSDateSel1 are correctly formatted to the same as estimated_delivery_date. You could also try:

=Sum({$<estimated_delivery_date = {">=$(=Date(vDateSel)) <=$(=Date(vDateSel1))"}, tl_type={'Upgrade'}, tl_type={'New Business'}, date>} tarv)

Or add explicit formatting to the Date() statements to align with estimated_delivery_date.