7 Replies Latest reply: Jul 23, 2012 4:50 AM by Eddie Morah RSS

    Set Analysis: Nested If Statement into Set Analysis

    Eddie Morah

      Hi,

       

      Can anyone help me to convert this If Statement into Set Analysis Expression

       

      Sum(If(

      Type='Cancelled' and

           Ceil(Month(Date(Floor([Ordered Date])))/3)=

           (If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,1,(Ceil(Month(Date(Floor([Fiscal Date])))/3)+1)))

             and 

             Year(Date(Floor([Ordered Date])))=

             (If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,(Year(Date(Floor([Fiscal Date])))+1),Year(Date(Floor([Fiscal Date])))))

             ,

                 ([Amount]),0))

       

       

      Thanks

        • Re: Set Analysis: Nested If Statement into Set Analysis
          marco masin

          Sum({1}  If(

          Type='Cancelled' and

               Ceil(Month(Date(Floor([Ordered Date])))/3)=

               (If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,1,(Ceil(Month(Date(Floor([Fiscal Date])))/3)+1)))

                 and 

                 Year(Date(Floor([Ordered Date])))=

                 (If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,(Year(Date(Floor([Fiscal Date])))+1),Year(Date(Floor([Fiscal Date])))))

                 ,

                     ([Amount]),0))

          You can post your project beacuse  id like saw the data model

            • Re: Set Analysis: Nested If Statement into Set Analysis
              Eddie Morah

              Thank you...This is good,

               

              But I'm looking for more of a complete set analysis statement, specially on the IF statements.

                • Re: Set Analysis: Nested If Statement into Set Analysis
                  Carlos Lisboa

                  Hi,

                   

                  It would be easier for us to help you if you tell us what you're trying to do with all that Ceil(Month(Date(Floor([Ordered Date])))/3).

                  I looks like those parts can be replaced by some tweaks in you Calendar table.

                   

                  Regards,

                  Carlos

                    • Re: Set Analysis: Nested If Statement into Set Analysis
                      Eddie Morah

                      What i'm trying achieve by this is.... Sum of Cancelled Amount in Next Quarter.

                       

                      Sum(If(

                      Type='Cancelled' and   

                           Ceil(Month(Date(Floor([Ordered Date])))/3)=

                           (If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,1,(Ceil(Month(Date(Floor([Fiscal Date])))/3)+1)))

                             and

                             Year(Date(Floor([Ordered Date])))=

                             (If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,(Year(Date(Floor([Fiscal Date])))+1),Year(Date(Floor([Fiscal Date])))))

                             ,

                                 ([Amount]),0))

                       

                       

                      Ceil(Month(Date(Floor([Ordered Date])))/3) is for Quarter Calculation.

                       

                      (If(Ceil(Month(Date(Floor([Fiscal Date])))/3)=4,1,(Ceil(Month(Date(Floor([Fiscal Date])))/3)+1)))

                      In this one I'm checking if the Quarter is of 4 then Make it 1, Which would be next quarter for 4, Else add it with 1. where i would get next quarter

                       

                      Same way im doing for the year.

                      If Quarter is of 4th, then Increase year also by 1. by which i get 1 quarter in next year.


                        • Re: Set Analysis: Nested If Statement into Set Analysis
                          Carlos Lisboa

                          So, there's a simpler and more straight forward way to do that

                          Here's my take on this, i supose you have a calendar table, in that table you can create a Quarter field.

                          Something like this:

                           

                          'Q' & ceil(month(Date)/3)  as Quarter,

                           

                          Now, to do what you need to do the most simple way is to create a field like 20121 (YearQuarter)

                           

                          year(Date) & ceil(month(Date)/3)  as YearQuarter,

                           

                          And now to be able to cycle trough all quarters sequentially you can do:

                           

                          AutoNumber(year(Date) & ceil(month(Date)/3))  as YearQuarterSeq,

                           

                          This will assign a sequential number for each YearQuarter.

                          Now on you expression you should be able to simply do this:

                           

                          sum( {< Year =, Month =, Quarter =, Type = {"Canceled"}, YearQuarterSeq = {"$(=max(YearQuarterSeq+1))"}>} Amount)

                           

                          Hope this helps, or at least points you to the right direction.

                           

                          Regards,

                          Carlos

                  • Re: Set Analysis: Nested If Statement into Set Analysis
                    Adam Abwat-Johnson

                    Hi,

                     

                    I'd probably consider moving some of the calculations you are doing into the script which will make things easier to follow. So instead of having Ceil(Month(Date(Floor([Ordered Date])))/3)  put that into your script and give it a field name. In fact, you may even want to move most of the calculation into script, all the bits inside the SUM() if you can.

                     

                    This may have a small impact on the time to reload the document but will help in the long run when people come to use it as the front end will be quicker for it. As a general rule try to put as much into the script as possible.

                     

                    Do this first and it may help you work out what you need to do for the set analysis. It will certainly help you be able to see what you're doing more clearly.

                     

                    Once you've done this, or if you are having trouble working out how to do this, post back here and we can all have a look. Don't worry too much also, set analysis takes a little effort getting your head around but once you've got it you'll find it a big help as QlikView is really geared up for this sort of thing.

                     

                    Chris