15 Replies Latest reply: Sep 16, 2011 7:37 AM by Stefan Wühl RSS

    Another Set Analysis Q..

      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.

        • Re: Another Set Analysis Q..
          Deepak Vadithala

          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..
              Stefan Wühl

              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..
              Leni Balakrishnan

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

                **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..
                    Stefan Wühl

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

                        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

                         

                        to

                         

                        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..
                            Stefan Wühl

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

                                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..
                                    Stefan Wühl

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

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

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

                                            • Another Set Analysis Q..
                                              Stefan Wühl

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

                                                  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..
                                                      Deepak Vadithala

                                                      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..
                                                        Stefan Wühl

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