Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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‌@

4 Replies
vardhancse
Specialist III
Specialist III

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)

swuehl
MVP
MVP

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

Not applicable
Author

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

vardhancse
Specialist III
Specialist III

Hi

If you want to setup whole QlikView you can contact the Qlik sales.