Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

comfort1
New Contributor

KPI to return value of cell in excel spreadsheet

Hello, I'd like to post 2 KPIs...one showing the weekly average of sales for the year and one  showing last week's sales. The weekly average for the year is simple, but is there an expression that will return the value of a specific cell in the spreadsheet?

Thank you!!

1 Solution

Accepted Solutions
tomasz_tru
Valued Contributor

Re: KPI to return value of cell in excel spreadsheet

Can you give more details?

Regards,

Tomasz

9 Replies
tomasz_tru
Valued Contributor

Re: KPI to return value of cell in excel spreadsheet

Can you give more details?

Regards,

Tomasz

ahaahaaha
Honored Contributor

Re: KPI to return value of cell in excel spreadsheet

Hi Gorden,

May be like this

Sum({<Date={'10.05.2017'}>}Sale),

where Sale - field with value sale,

Date - field with date of analisys last week (this is provided that you use the Master calendar).

Regards,

Andrey

comfort1
New Contributor

Re: KPI to return value of cell in excel spreadsheet

Hello Tomasz, I'm using an excel spreadsheet for my data and I'd like to show the sales from last week which is located at cell C2. I wish there was some way that I can show a KPI that will just show that cell value as a KPI from that cell. I update the spreadsheet once a week so the most current data will always be on the top line.

I want to show a KPI for last week and a KPI that is an average per week for the year. I know the bar graph shows last week's value, but its just to small as a visual when I'm presenting the info at our weekly meeting.

Appreciate your help!!! Thank you

tomasz_tru
Valued Contributor

Re: KPI to return value of cell in excel spreadsheet

I thin you can

A.

load only first row of a table:

FIRST 1 LOAD

    Dollars AS KPIvalue

FROM [lib://FILE.xlsx]

(ooxml, embedded labels, table is sheet1);

B.

or use SET ANALYSIS to select value corresponding to the maximum date in the whole table

comfort1
New Contributor

Re: KPI to return value of cell in excel spreadsheet

Tomasz, so I would just load the entire expression as it is written in the expression field?

FIRST 1 LOAD

    Dollars AS KPIvalue

FROM [lib://FILE.xlsx]

(ooxml, embedded labels, table is sheet1);

Sorry I'm a newbie to Qlik Sense. I love it, but it's difficult to understand.

OmarBenSalem
Esteemed Contributor

Re: KPI to return value of cell in excel spreadsheet

You can add a week field in your script:

week(Date) as week;

and then in the expression,

sum({<week={"$(=max(week))"}>}KPI)

please refer to this :

YTD, MTD issue

Or you can add your data through the data load editor (the wizard) and PREPARE your data before reloading!

Don't forget to PREPARE DATA to tell Qlik to PREPARE some things for you (AUTO CALENDAR, GeoData..)

Qlik Sense will create an auto calendar for you that you can use to build such expressions:

exp:

right click on your desired measure :

Capture.PNG

choose what type of measures you want to have  :

Capture.PNG

You'll find it as a master measure, just drag it, and you'll have your Last Week KPI:

Capture.PNG

mov
Esteemed Contributor III

Re: KPI to return value of cell in excel spreadsheet

As I understand, you need to show in your KPI the Dollars in the latest week. The cells in excel don't matter, and the data is already in your application.  All you need to do as to write a proper expression in KPI.

Assuming that the "Run Date" identifies weeks, it could be something like this, if I got the field names right:

sum({<[Run Date]={"$(=date(max({1} [Run Date])))"}>} Dollars)

tomasz_tru
Valued Contributor

Re: KPI to return value of cell in excel spreadsheet

No, Gorden- it's for load script. Just load the table with only one row of one field. Then you can use it in the app with any aggregation.

comfort1
New Contributor

Re: KPI to return value of cell in excel spreadsheet

Awesome!! That worked perfectly!!!

Thank you so much Michael for the help!!!!!

Community Browser