10 Replies Latest reply: Jul 8, 2011 2:34 PM by John Witherspoon RSS

    Current Day

      Hello,

       

      I currently have a linearity chart that has the sums of three Fiscal Quarters.  I also have the field [Day # in FQ], and so for the current quarter (2012 Q2), we are only up to day 46 for example.  I need to create a chart that shows the total sums of [TOTAL REVENUE ($)] for all three quarters that the linearity chart is summing, but only up to the most current day in the most current fiscal quarter.  So all three sums would be through day 46 in my example.  Please let me know if you would have any idea how to go about accomplishing this, and if I can give you any more helpful information, and thank you so much for all your help.

       

      Sorry I cannot upload any examples of my data because it contains confidential information regarding a publicly traded company.

       

      Alex

        • Current Day

          Hi,

           

          Can you make a small QlikView application, that would ilustrate the issue?

          It will help us to understand what you want to achieve.

           

          Kind regards,

          Janusz

            • Current Day

              Ok here was the advice I was given by my dashboard support team.  Unfortunetly they only have 2 people to help about 100 people, and so their responses are a bit delayed and not very detailed.  Can anyone help me to interpret this using a specific examples? 

               

              "As for creating the flag in the calendar, this needs to be handled in the script. The pusedcode would be: What day number is it in the quarter?  Day X. If day number is <= Day X then Y or else N. This will give you a flag for every date in your calendar that tells you if the day in the quarter has passed or not, and then aggregating over this flag where it is Y will give you relative quarter to dates."

               

              I'm assuming they are telling me to create an variable based on the day in the current quarter (vDay#CurrentQTR), and then somehow use this variable to sum the revenue, with a null if the x> vDay#CurrentQTR?

               

               

              Thanks,

              Alex

                • Re: Current Day
                  John Witherspoon

                  I believe they're telling you to do this in the script where you load your calendar table:

                   

                  ,if(daynumberofquarter(Date)<=daynumberofquarter(today()),'Y','N') as QTDFlag

                   

                  And this in the chart expression:

                   

                  sum({<QTDFlag*={'Y'}>} Revenue)

                    • Current Day

                      this is what i currently have written based on your recomendation John, and i have it in the load script on my main tab under the default system variables.  I assume I'm doing something wrong as it's not loading and the as function is not the proper color.  Any advice would be greatly appreciated.  Thank you

                       

                      set vCurrentDay= if({{<[Day # of FQ]{'Q2 2012'}>}<=[Day # of FQ](today()),'Y','N') as QTDFlag;

                       

                       

                      Alex

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                       

                        • Current Day
                          Nagaian Krishnamoorthy

                          Set analysis expressions do not work in load script. They work only in chart expressions.

                           

                          So try to use script expression given by John or some variant of it based on your requirements.

                            • Current Day

                              Sorry but I am fairly new to this, so in my load statement if statements do not seem to be working. It says the if statement is bad, and i cannot load my script.  I put the statement after the [Fiscal Quarter and Year],

                              if

                               

                              ({{<[Fiscal Quarter and Year]{'Q2 2012'}>}<=[Day # of FQ](today()),'Y','N') as QTDFlag,

                              and then it won't load correctly, where should i be putting the statement?

                              thanks,

                              alex

                               

                               

                                • Current Day

                                  Ok, I think the problem is the format of the data.  currently I have a field

                                   

                                  [Fiscal Quarter and Year], which returns values such as '2012 Q2'

                                   

                                  then linked to this is the field; [Day # in FQ], which returns values such as '32'

                                   

                                  and I need to format these two fields into one readable by the today()) function

                                   

                                  thanks,

                                  alex

                                    • Current Day
                                      John Witherspoon

                                      Daynumberofquarter() is a QlikView function.  You don't replace it with your field names, and you don't add set analysis syntax to it.  You should have a calendar table.  This calendar table should have a field like "Date", or perhaps "Fiscal Date" since you're talking about a fiscal calendar.  You may need to use a second parameter on the daynumberofquarter() function if your fiscal quarter starts on some off month like November, say.

                                        • Current Day

                                          Actually the fiscal calander starts off in February, and it has the fields 'Date',  and 'Day # in FQ'

                                           

                                          Sorry so what would my second parameter look like?

                                           

                                          Thanks,

                                          Alex

                                            • Re: Current Day
                                              John Witherspoon

                                              The 2 says that your fiscal calendar starts with February:

                                               

                                              ,if(daynumberofquarter(Date,2)<=daynumberofquarter(today(),2),'Y','N') as QTDFlag

                                               

                                              That goes in the load script where you load your calendar, where you load your Date field into the calendar table.  This is one more field you are loading, just like the other fields in your calendar.