4 Replies Latest reply: Feb 15, 2017 4:36 AM by varaha sasi vardhan kandragula RSS

    dashboard autoupdating from a .xls

      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
          varaha sasi vardhan kandragula

          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

              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