topic Need help with set analysis in QlikView

Need help with set analysis

Thu, 04 Aug 2011 17:52:03 GMT

New to Qlik view.Please help

need to find increase/drop in sales between 2 dates selected from 2 different list boxes

(salesnew-salesold)/salesold

Re: Need help with set analysis

Thu, 04 Aug 2011 18:10:28 GMT

may be i did not explained it enough

user can pick 2 diff. dates from 2 diff list box and the result should be the drop/increase in sales.

(salesWeek1-salesweek2)/salesweek1

Message was edited by: sandeepteotia File is attached

Need help with set analysis

Thu, 04 Aug 2011 18:20:11 GMT

Sandy,

Might be easier if you recreate this in another qvw and upload it, or just post the one you're working on. As you have explained it, it's tough to tell exactly what you're going for. There may be a better way to achieve your solution than you have considered.

Need help with set analysis

Thu, 04 Aug 2011 18:39:35 GMT

Hi Sandy,

if i understand you correctly, i use a similar function in our main application.

We did not link the calendar dimension table to facts table. We use two variables holding the selected min and max date from the calendar listbox (only 1 calendar is used, and with min and max user selections of more than 2 values are compensated)

Your needed expression would then look like

sum({$< factsdate = {'=$(varDate1)'} >} sales) - sum({$< factsdate = {'=$(varDate2)'} >} sales) / sum({$< factsdate = {'=$(varDate1)'} >} sales)

The expression should work (except syntax errors) as long as you don't put a date(part) in the dimension

As i actually don't have access to a designer, i cannot test it

Need help with set analysis

Thu, 04 Aug 2011 18:59:37 GMT

Hi Noster,

thank yu Noster.You mean the user need to select 2 dates from the same calendar listbox and then save them in 2 diff. variables

Need help with set analysis

Thu, 04 Aug 2011 20:22:19 GMT

Hi Noster,

its picking the max and min weekend but the sales is picked as same amount.so the resultset is 0.

Am i missing something.

=sum({$< Weekend = {'=$(vWeekend1)'} >} Amount)-sum({$< Weekend = {'=$(vWeekend2)'} >} Amount)

Need help with set analysis

Thu, 04 Aug 2011 20:38:49 GMT

Put the Set Analysis expressions in separate columns to locate errors

Week1 : sum({$< Weekend = {'=$(vWeekend1)'} >} Amount)

Week2: sum({$< Weekend = {'=$(vWeekend2)'} >} Amount)

and check if their results are correct. Perhaps it's justed caused by missing brackets or the like

Re: Need help with set analysis

Thu, 04 Aug 2011 21:25:05 GMT

Hi Nostor,

File is attached. I tried doing that.I missed something.

thank you very much for your help.

Re: Need help with set analysis

Fri, 05 Aug 2011 07:21:26 GMT

you can find the right syntax in the attachement.

There was just one waste equals sign in the expression, the correct expression should be

SUM({$< Weekend1 = {"$(vWeekend1)"}>} Amount)

greets,

Max

Re: Need help with set analysis

Fri, 05 Aug 2011 14:04:49 GMT

it works. thanks Max!!!!!!!!!!