topic Re: Another Set Analysis Q.. in QlikView

Another Set Analysis Q..

Mon, 12 Sep 2011 22:51:59 GMT

I am very new to qlikview and looking for some help writing a following expression using set analysis. Please pardon me if several question like such are already being posted, I tried my best to use the knowledge from all I could find but still could not resolve on my own. Thanks

To put it in a simple way, I have a list box and a line graph in sheet. Currently, if there is no selection, following is the expression that calculates the line graph.

Sum(if(RET_NAME = 'ABC' AND (PART_SK ='1' or PART_SK ='2' or PART_SK = '7' or PART_SK ='6' or PART_SK ='0' or PART_SK ='5' or PART_SK ='90' or PART_SK ='79') AND (WEEK_END_DATE > Date(WeekEnd(Today(),-1)-70)) AND WEEK_END_DATE < Date(WeekEnd(Today(),-1)),PART_COUNT))+Sum(if(RET_NAME = 'ABC' AND PRICE >0 AND (WEEK_END_DATE > Date(WeekEnd(Today(),-1)-70))

AND WEEK_END_DATE < Date(WeekEnd(Today(),-1)),PART_B_COUNT))

Now, the scenario needs to be, regardless of the selection in the list box, I should be able to see what I was seeing before in the Line Graph.

Here is what I tried, but did not work.

Sum({1<RET ={'ABC'},PART_SK = {1,2,7,6,0,5,90,79}, WEEK_END_DATE = {"=$(>Date(WeekEnd(Today(),-1)-70) < Date(WeekEnd(Today(),-1)))"}>} PART_COUNT ) + Sum({1<RET ={'ABC'},PRICE={" >0"},WEEK_END_DATE = {"=$(>Date(WeekEnd(Today(),-1)-70) < Date(WeekEnd(Today(),-1)))"}>}

PART_B_COUNT)

Might be pretty simple, but not sure what am I missing.

Re: Another Set Analysis Q..

IAMDV — Tue, 13 Sep 2011 08:43:20 GMT

Hi Rohit,

You can get the Set Analysis working for the above situation. However, it will be easy to look at sample document. So please can you post the QV document?

Meanwhile, if you wanted to ignore all the selection for a chart. You need to use something like this....

=COUNT({$<[$(=Concat({1<$Field-={'Gender'}>}distinct $Field,'],[')&']=')>} Resp_ID)

In the above example, except Gender every field selection will be ignored. Hope this makes sense.

Cheers - DV

Re: Another Set Analysis Q..

swuehl — Tue, 13 Sep 2011 09:06:35 GMT

Hi D V and arorarohit10,

if it's all about ignoring all selections except one field, I think something like this

=count{1<Gender= p(Gender) >} Resp_ID)

should work and might be easier to read after a while.

But I think there is more about it, without trying the code myself, try maybe soemthing like:

=Sum({1<RET ={'ABC'},

PART_SK = {1,2,7,6,0,5,90,79},

WEEK_END_DATE = {">$(=Date(WeekEnd(Today(),-1)-70)) <$(=Date(WeekEnd(Today(),-1))))"}>}

PART_COUNT )

+ Sum({1<RET ={'ABC'},

PRICE={" >0"},

WEEK_END_DATE = {">$(=Date(WeekEnd(Today(),-1)-70)) <$(= Date(WeekEnd(Today(),-1))))"}>}

PART_B_COUNT)

Note how I changed the Seach string for the WEEK_END_DATE (putting the comparison out of the dollar sign expansion, changing to $(= and using two dollar sign expansions.

Not sure if this works or is all that needs to be done, though.

Ah, and take care that your WEEK_END_DATE matches the format of your dollar sign expansions.

Regards,

Stefan

Another Set Analysis Q..

Anonymous — Tue, 13 Sep 2011 10:13:30 GMT

Hi,

Just try this Set analysis:

sum({1<RET_NAME = {'ABC'},PART_SK = {'1','2','7','6','0','5','90','79'},WEEK_END_DATE = {'>$(Date(WeekEnd(Today(),-1)-70))'}, WEEK_END_DATE = {'<$(Date(WeekEnd(Today(),-1)))'}>}PART_COUNT)

sum({1<RET_NAME = {'ABC'},PRICE = {'>0'},WEEK_END_DATE = {'>$(Date(WeekEnd(Today(),-1)-70))'}, WEEK_END_DATE = {'<$(Date(WeekEnd(Today(),-1)))'}>}PART_B_COUNT)

Re: Another Set Analysis Q..

Tue, 13 Sep 2011 17:09:38 GMT

**Please note that the list box has just the RET_NAME values.**

Thanks all for your valuable input. Here is what I was able to implement so far.

In the script section, declared the valiables for date calculations

LET vWeekStartDate = Date(weekend(Today(),-1)-70);

LET vWeekEndDate = Date(weekend(Today(),-1));

In the Expression of Line Chart

Sum({1<RET_NAME ={'ABC'}, WEEK_END_DATE = {">$(vWeekStartDate) < $(vWeekEndDate)"}, PART_SK = {'1','2','7','6','0','5','90','79'}>} PART_COUNT )

If I just use first two conditions, it worsk fine ( the graph appears), as soon as I add the 'underlined' expressions, graph disappears. Looks like PART_SK expression is causing the issue. Not sure how it was working as mentioned above in the original post.

Re: Another Set Analysis Q..

swuehl — Tue, 13 Sep 2011 17:42:53 GMT

Hi arorarohit,

can't see an issue with your expression (but maybe I'm just missing something), except that your original expression had two sums.

(second part like

+ Sum({1<RET ={'ABC'}, PRICE={" >0"}, WEEK_END_DATE = {">$(=Date(WeekEnd(Today(),-1)-70)) <$(= Date(WeekEnd(Today(),-1))))"}>} PART_B_COUNT)

)

I assume you were just posting not the complete expression, but if you do, are you sure that the combinations of field selections really result in a value? Maybe, in your original version, you just got a result because of the second part?

Regards,

Stefan

Re: Another Set Analysis Q..

Tue, 13 Sep 2011 20:18:00 GMT

Thanks Stefan,

You are right on the part that I did not post complete expression in my second post. This is because (and I should have mentioned that before) that second part after '+' sign is giving values BUT right graph should be the combination of both first and second, and I am not getting any value out of first expression only. Where as the first expression was giving right results when the expression was as posted in the original post.

I am also not able to find the cause of problem with following conversion

AND (PART_SK ='1' or PART_SK ='2' or PART_SK = '7' or PART_SK ='6' or PART_SK ='0' or PART_SK ='5' or PART_SK ='90' or PART_SK ='79') AND

PART_SK = {'1','2','7','6','0','5','90','79'}

Thanks for help though, I guess I'll keep digging more.

Another Set Analysis Q..

swuehl — Tue, 13 Sep 2011 22:21:27 GMT

With this expression, do you get zero values in your line graph (you could also convert it to a straight table for examination) or something like no data to display?

Could you post your app here at the forum (upload is available in advanced editor)?

Another Set Analysis Q..

Wed, 14 Sep 2011 17:17:21 GMT

Thanks Stefan for your continious help.

I am getting 'No Data to display'.

I am sorry but I cannot upload my app as it is confidential data. The expression seems so straight forward, but I am not sure what exactly I am missing here.

Thanks again all for help.

Another Set Analysis Q..

swuehl — Wed, 14 Sep 2011 17:34:19 GMT

I think you already did, but could you try to select the assigned field values of your set expression in the appropriate field list boxes and look at the resulting record set regarding PART_COUNT?

What dimensions do you use in your line graph?

Could you post your data model here?

All this is kind of desperate trial, just have the impression that we are missing something obvious.

Regards,

Stefan

Another Set Analysis Q..

Thu, 15 Sep 2011 19:03:57 GMT

Hi Stefan,

Here are more details,

The list box consists of only 'RET_NAME' from data source.

There is only one dimension used in the Graph - WEEK_END_DATE ( so it shows the dates of last 10 rolling Mondays)

I tried to create a table, as suggested, so see the output and mostly it is '0'. The only thing I could nail it down to is that

AND (PART_SK ='1' or PART_SK ='2' or PART_SK = '7' or PART_SK ='6' or PART_SK ='0' or PART_SK ='5' or PART_SK ='90' or PART_SK ='79') AND

this part of the expression is a problem.

I tried writing the expression in different for as below, then it jsut shows '-' instead of zero in the table.

IF ((PART_SK ='1' or PART_SK ='2' or PART_SK = '7' or PART_SK ='6' or PART_SK ='0' or PART_SK ='5' or PART_SK ='90' or PART_SK ='79'), Sum({1<RET_NAME ={'ABC'}, WEEK_END_DATE = {">$(vWeekStartDate) < $(vWeekEndDate)"}>} PART_COUNT )

I know it is difficult to decipher the exact issue without QVW file or model, but there is not much choice.

Thanks for your help

Another Set Analysis Q..

Thu, 15 Sep 2011 20:19:41 GMT

You can go to Settings,Document properties,Scrambling and scramble the sensitive fields so you can post it.

Another Set Analysis Q..

swuehl — Thu, 15 Sep 2011 22:10:02 GMT

Hi,

I think you should combine the if with the set expression like this:

Sum({1<RET_NAME ={'ABC'}, WEEK_END_DATE = {">$(vWeekStartDate) < $(vWeekEndDate)"}>}

if(PART_SK ='1' or PART_SK ='2' or PART_SK = '7' or PART_SK ='6' or PART_SK ='0' or PART_SK ='5' or PART_SK ='90' or PART_SK ='79',PART_COUNT ))

In my previous post, my intention was that you create a tablebox with your fields incl. PART_COUNT (i.e. you might need to temorarily add some list boxes), then create list boxes for all the fields used in the set expression and select the set as used in your set expression. Then look what the resulting table box shows.

Maybe posting the app with scrambeld fields is possible?

Regards,

Stefan

Another Set Analysis Q..

Fri, 16 Sep 2011 00:50:58 GMT

You hit the bulls eye.

However, I am really interested to understand why we had to write this code as you mentioned. Is there any standard way of writing some piece into set analysis and other like you suggested.

Doesn't PART_SK = {'1','2','7','6','0','5','90','79'} covers the same logic as written explicitly later in your comment.

Appreciate your help.

Re: Another Set Analysis Q..

IAMDV — Fri, 16 Sep 2011 08:08:07 GMT

I am glad it worked. I have been following this thread since few days. I really wanted to appreciate Stefan's help to get this expression working. Rohit (arorarohit10) is unable to post the sample QV document neither the data model, I understand it is confidential data. But both of you guys worked together and got it working.

Stefan - You are awesome. Very helpful and most important being patient. Nice way to get past 3000 mark - Congratulations.

Cheers - DV

Another Set Analysis Q..

swuehl — Fri, 16 Sep 2011 11:37:28 GMT

Hi arorarohit10,

Well, my last expression is probably solving your business problem (disregarding current selections on the chart), but is only half way from your original expression with just adding set identifier {1} (see below a)) to a equivalent full set expression solution.

Maybe we were too focused on solving the set expression issue and you might live well with the proposed solution still using an if().

I am also quite interested why the set expression did not work, so if you have any chance, it might be good if you could post your sample app here (with scrambled or simulated data maybe? If you don't trust the scrambling, you might be able to load simulated records into your app).

And I think I haven't understood your last comment completely, could you clarify your question (if any) please?

Regards,

Stefan

a) like:

=Sum({1} if(RET_NAME = 'ABC' AND (PART_SK ='1' or PART_SK ='2' or PART_SK = '7' or PART_SK ='6' or PART_SK ='0' or PART_SK ='5' or PART_SK ='90' or PART_SK ='79') AND (WEEK_END_DATE > Date(WeekEnd(Today(),-1)-70)) AND WEEK_END_DATE < Date(WeekEnd(Today(),-1)),PART_COUNT))