4 Replies Latest reply: Feb 15, 2017 4:36 AM by Sasi Vardhan KV RSS

    dashboard autoupdating from a .xls

    davide tufu

      Ciao All,

       

      I'm having a problem creating a dashboard for my boss ( i'm not skilled in Qlik ).

       

      Actually I have a xls file with a list of orders from last 3 year and all incoming orders. so as per example:

       

      Customerorder numberorder datematerial ordered quantitypriceshipping dateamount
      coca colazz1012-02-2017 sugar10112-03-201710€

       

       

      What i need is to have a dashboard with :

       

      1) comparison of total turnover year over year ( 2015 vs 2016 ) --> chart

      2) chart splitted with % sold ( for example : cocacola 100% sugar, or 20% sugar 80% water )

      3) comparison year over year of product ordered

       

      same as previous 3 points but I would like to enter better in details ( as per example month over month comparison ) 2015 vs 2016 vs 2017

       

      then finally It will be fantastic If once a month I update the xls file and automatically the dashboard auto-updates.

       

      Is there anybody can help me?

      swuehlmaxgro@

        • Re: dashboard autoupdating from a .xls
          Sasi Vardhan KV

          you need to have one master calendar, based on the the data what you are getting in excel.


          Note: The format defined in SET DateFormat and your date field format in Qlikview Table should be same, then only this expressions works.

           

          Last 5 Years Sales

          Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -4))<=$(=Date(Max(Date)))’}>} Sales )

           

          Last 6 Quarters Sales

          Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -5))<=$(=Date(Max(Date)))’}>} Sales )

           

          Last 12 Months Sales

          Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -11))<=$(=Date(Max(Date)))’}>} Sales )

           

          Last 15 Weeks Sales

          Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -14))<=$(=Date(Max(Date)))’}>} Sales )

           

          Last 10 Days Sales

          Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=Date(Max(Date)-9))<=$(=Date(Max(Date)))’}>} Sales )

           

          Note:

          YTD - Year to Date

          QTD - Quarter to Date

          MTD - Month to Date

          WTD - Week to Date    

           

          Last YTD for the period selected

          Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=YearStart(Max(Date), -1))<=$(=AddYears(Max(Date), -1))’}>} Sales)

           

          Last QTD for the period selected

          Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=QuarterStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -3))’}>} Sales)

           

          Last MTD for the period selected

          Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Max(Date), -1))<=$(=AddMonths(Max(Date), -1))’}>} Sales)

           

          Last WTD for the period selected

          Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=WeekStart(Max(Date), -1))<=$(=Date(Max(Date) -7))’}>} Sales)

          • Re: dashboard autoupdating from a .xls
            Stefan Wühl

            Hi Davide,

             

            welcome to Qlik community!

             

            Reading your request, it seems you are basically in need of someone to build the whole application

             

            I believe it would be better to go to your boss, tell him that you need to learn QlikView and get his support.

             

            There is a lot of free / paid material that get's you started.

             

            QlikView Free Training - Business Intelligence (BI) Reporting Software Solutions | Qlik

            How to become a QlikView Developer?

            Books and literature

            https://www.askqv.com/blogs/

             

            You should start with the basics, and I personally think it's not a good thing to do this using the forum.

            It's much more effective and efficient if you read a good book or take a short (free) tutorial.

             

            If you then have specific problems / questions, we can help you!

             

            Best regards,

            Stefan

            • Re: dashboard autoupdating from a .xls
              davide tufu

              thanks for all your reply..

               

              since it will be only one dashboard.. is there anybody that can give me his cost to develop the platform?

               

              thanks in advance