Another Set Analysis Q..

[#1]

Hi

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.

[#2]

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

[#3]

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

[swuehl · Accepted solution]

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

[#5]

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.

[IAMDV]

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

[swuehl]

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