5 Replies Latest reply: Oct 29, 2013 3:55 PM by Steve Dark RSS

    Getting previous period in set analysis



      I posted my query in a previously discussed blog between QV colleagues but decided to also create a new one just in case those members are not available.


      I have two variables that find the equivalent min and max period of the prior year based on the current period selected. Eg. If the user selects Nov-13 till Dec-13, then each of my variables store Nov-12 and Dec-12 respectively.

      As with the other guys, my variables work in theory in terms of getting the correct periods based on what was selected. Getting it to work in QV set analysis isn't so straight forward however.

      I tried the ONLY function as per this blog (even though I am aware that it will not allow multiple selections).

      I could not get it to work. Nonetheless, the user will need to make more than one selection and I am assuming continuing periods only, i.e. Aug-13 till Dec-13 as an example and not also add Feb-13 to this (I would prefer a solution for this as well but it's not critical).


      Please see below a snippet of my set analysis:


      sum({$<MonthSeq = {">=$(=vMinMonthSeqPriorYear)<=$(=vMaxMonthSeqPriorYear)"}>} [Gross Sales (Excl)] )

      using "ONLY", I coded:

      sum({$<MonthSeq = {">=$(=only(vMinMonthSeqPriorYear))<=$(=only(vMaxMonthSeqPriorYear))"}>} [Gross Sales (Excl)] )


      Either way, my results return zero. Would appreciate any assistance.



      Thank you!

        • Re: Getting previous period in set analysis
          Steve Dark

          You might want to take a look at these two documents that I have uploaded:






          The thing to watch when using dates in Set Analysis is that the text representation of the date must exactly match the natural format of the field you are matching to.  It is worth using a Date function in your load script to force a specific format and also in your variables to ensure you are matching to a specific format.


          Hope that helps,


            • Re: Getting previous period in set analysis

              Hi Steve.


              Thanks so much for your assistance!


              I also went through your documents. The one where you used variables in your load script is similar to what I have been doing, but instead on the front end. I am basically working offline so without being able to reload data, I decided to use variables on the front. I am pretty comfortable that the date formatting is correct as other aspects of the model work fine.


              When you for instance select a sequence number of say 50 (the sequence number relates to my dates and also as you pointed out would be safer than relying on erroneous dates if applicable), the set analysis displays the sum based on the value 50, even though my set analysis variable says takes the sequence number that was selected and minus 12 to get prior year's sum.


              Please advise if there's anything else that I could possibly consider or maybe something I hadn't quite picked up from what you suggested.


              Many thanks.

                • Re: Getting previous period in set analysis
                  Steve Dark



                  Thanks for taking a look at those documents - I'm glad you found them helpful.


                  It is always worth doing as much work as possible in the load script.  If you are not able to connect to the original data source the best way to do this is with a Binary Load.  This loads the data model from a QVW file into another QVW file. From here you can further manipulate the data by adding new tables or doing RESIDENT loads to add to the data you already have.


                  To create one of these go to the Edit Script dialog and click the QlikView File button.  This will allow you to select a QlikView file to load from.  It will then add a line like this to the top of your load script:


                  Binary [d:\documents\quick intelligence\billableitems.qvw];


                  This line must remain the first line, you can not even insert a column before it.


                  Binary loads are lightening quick and give you the data model in its entirety from the other document.  You can then carry on with further load script statements.


                  Hope that helps,




                    • Re: Getting previous period in set analysis

                      Hi Steve.


                      I just managed to find the problem now ... in the set analysis, the variables I created (beginning with an equal to sign) predetermine the sequence numbers for the current selection and thus inadvertently for the prior year's selection.

                      It would seem that my set expression was fine so far as the brackets, equal to signs etc were concerned, however since the user does not actually make selections via sequence numbers but rather through the date field(MonthYearShort), I needed to override the date, i.e.

                      ({$<MonthSeq = {">=$(vMinMonthSeqPriorYear)<=$(vMaxMonthSeqPriorYear)"}, MonthYearShort=>} [Gross Sales (Excl)] )



                      On that note on binary loads: yes, I am familiar with it but since this model has passwords, I find that my binary loads don't work. I did try reading on the forums previously but did not find a definite answer. Do you know if there's a different procedure for binary loading off a password protected model?


                      Thank you for all your help.

                        • Re: Getting previous period in set analysis
                          Steve Dark

                          No problems.  Glad you have now got a working solution.


                          Regarding binary loads with passwords, there is a check box when enabling Section Access to disallow Binary Loads.  If this has been checked then that option is not available to you.


                          I should have spotted that you might have had date fields to ignore in your set analysis script.  Often there are many variants to ignore, and I will put these into a variable, as it may be used many times.  So, a variable vIgnoreDates may have something like the following in it:




                          And then set analysis for a single day may read:




                          Hope that helps.