topic Re: dashboard autoupdating from a .xls in QlikView

dashboard autoupdating from a .xls

Tue, 14 Feb 2017 09:50:52 GMT

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:

Customer	order number	order date	material ordered	quantity	price	shipping date	amount
coca cola	zz10	12-02-2017	sugar	10	1	12-03-2017	10€

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?

swuehl‌maxgro‌@

Re: dashboard autoupdating from a .xls

vardhancse — Tue, 14 Feb 2017 09:55:02 GMT

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

swuehl — Tue, 14 Feb 2017 10:08:25 GMT

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

Wed, 15 Feb 2017 09:21:07 GMT

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

Re: dashboard autoupdating from a .xls

vardhancse — Wed, 15 Feb 2017 09:36:04 GMT

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