15 Replies Latest reply: Oct 25, 2016 5:49 PM by Ajay Krishnan Prabhakaran RSS

    QVD Total

    Deepanshu Chamoli

      Hello All

       

      Please help me in the below query.. Is it possible to calculate the running total after loading the data in qvd not in visualization.

       

      I need a running total on the basis of ALID.. which can be filter on CA, MA & DA.

       

      Please help me. I need an urgent help!!

       

      Thanks

        • Re: QVD Total
          Santiago Respane

          Hi,

          maybe there's something wrong with your attachment since it doesn't seem to be any.

           

          Please verify in order to help you.

          Kind regards,

            • Re: QVD Total
              Deepanshu Chamoli

              Hello Sir... I just wanted to know if the above query is possible or not.

               

              Attached is the file.. This is the QVD data.. In the visualization I need only three columns..

               

              Monthyear, Actual ALID and Cumulative ALID... It would be great if u can help me.

               

              Please guide

               

              Thanks

              • Re: QVD Total
                Deepanshu Chamoli

                Hello sir

                 

                Please find the attached file

                 

                On Oct 26, 2016 12:44 AM, "Santiago Respane" <qcwebmaster@qlikview.com>

                  • Re: QVD Total
                    Vineeth Pujari

                    Can you also post your expected output!

                    • Re: QVD Total
                      Santiago Respane

                      Hi,

                      Please find attached one of many possible solutions.

                       

                      Kind regards,

                        • Re: QVD Total
                          Deepanshu Chamoli

                          Sir somehow in my Qlik Sense desktop QVW file is not showing any sheets.

                           

                          Can you please help me out to post a Qvf or qvd file calculating the running total on the above sheet..

                           

                          Thanks a ton in advance..

                            • Re: QVD Total
                              Santiago Respane

                              Hi,

                              i dont have Sense installed in this machine so i proceed by copying loading script and expressions.

                               

                              • Load scrpt:

                              TEST:

                              LOAD *,

                                YEAR(PGLD) * 100 + MONTH(PGLD)  AS PERIOD

                              ;

                              LOAD DATE(PGLD) as PGLD,

                                   CLN,

                                   DIN,

                                   MAN,

                                   ALID FROM [YourFile] (ooxml, embedded labels, table is Sheet1);

                               

                              • Chart:
                                • create a chart with PERIOD,PGLD,MAN,DIN,CLN as dimensions
                                • add an expression as follows:
                                  • For current period =SUM({<PERIOD={'$(=YEAR(TODAY(1))*100+MONTH(TODAY(1)))'}>}ALID)
                                    • This will sum all the alid's for current period (201610 you have no data for this period)
                                  • Variable period
                                    • Create a variable called v.CurrentPeriod
                                    • Add an input box pointing to that variable
                                    • add expression =SUM({<PERIOD={'$(v.CurrentPeriod)'}>}ALID)
                                      • this will sum all alid's that correspond to the current period variable in YYYYMM format
                                  • For max period available
                                    • have a variable called v.MaxPeriod and set the value to
                                      • =MAX({1}PERIOD)
                                    • Add an expression =SUM({<PERIOD={'$(v.MaxPeriod)'}>}ALID)
                                      • This will sum all alid's for the greatest period available

                               

                              Hope one of this versions helps you.

                              Kind regards,

                                • Re: QVD Total
                                  Deepanshu Chamoli

                                  Thanks a lot sir!!

                                   

                                  I have few queries.. Sorry about that as I am not good enough in Sense till now .

                                   

                                  What I understand is load the data and put the data in resident table and calculate period in resident table.

                                   

                                  Sir you are using too many functions to calculate the running total. But if I am using the table where I am calculating the running total there should be only one function... Sir which function I need to use..

                                   

                                  One more query sir.. I want the filters for CLN, MAN and on single month or multiple months.. Is it possible??

                                   

                                  Currently I am calculating the running total in visualization so when I am selecting any particular month.. The running total not work.. Please help

                                • Re: QVD Total
                                  Ajay Krishnan Prabhakaran

                                  Hey there,

                                  You can do something like this:

                                   

                                  Test:
                                  LOAD Distinct PGLD, MAN,
                                       ALID
                                  FROM
                                  AT.xlsx
                                  (ooxml, embedded labels, table is Sheet1);


                                  Final:
                                  Load PGLD,
                                    MAN,
                                       ALID,
                                  ALID + Alt(Peek(RunningALID),0) as RunningALID
                                  Resident Test
                                  Order by PGLD,MAN ASC;

                                   

                                  Drop TAble Test;

                                   

                                  Basically you can use Peek function for running totals

                                    • Re: QVD Total
                                      Deepanshu Chamoli

                                      Thanks Ajay sir.

                                       

                                      Please help me to know ALT function.. Sir can I filter on month, year , quarter, CLN , DIN and MAN.. Using above logic

                                       

                                      Thanks

                                        • Re: QVD Total
                                          Ajay Krishnan Prabhakaran

                                          Sure.

                                          * Alt function is used to pick the first non-null value. The reason I used it is because while summing the first row peek will look for a row above that and wont find any hence will replace null with zero in that case(so it will be used only once)

                                          * When you filter if you want the Running Total to be re-calculated based on your filter then its gonna be impossible to cover all different combinations. Doing it on the UI is the easiest. May I ask why you would wanna do it in the script.

                                            • Re: QVD Total
                                              Deepanshu Chamoli

                                              Thanks a lot sir.. Sir in my current visualisation I am calculating the running total in visualization.

                                               

                                              I am using 3 columns

                                               

                                              1. Month

                                              2. Alid

                                              3. Running Slid

                                              I can easily filter on CLN, DIN, MAN.. But when I am selecting any particular month it is not calculating the running total. This might because by selecting particular month it is not considering the previous month values... And as per requirement I need to show via month quarter or year..


                                              This is the reason I end up calculating it in script so that I can filter up on month year or quarter..


                                              Please let me know if it make no sense to you..

                                                • Re: QVD Total
                                                  Ajay Krishnan Prabhakaran

                                                  Hey Deepanshu,

                                                  You could just create a calendar with Months,Quartes and Years.

                                                  You could also use QTD and YTD.

                                                  If that's the case you can just create these flags in the backend and use these as filters in the Front-end instead of selecting individual months.

                                                   

                                                  If(PGLD>= QuarterStart(today()),1,0) as QTD,

                                                  If(PGLD>= YearStart(today()),1,0) as YTD,

                                                  Ceil(Month(PGLD)/3) as Quarter,

                                                  Date(YearStart(PGLD),'YYYY') as Year,

                                                   

                                                   

                                                  Is my understanding correct.

                                                   

                                                  Thanks