KPI to return value of cell in excel spreadsheet

[Anonymous]

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!!

[tomasz_tru · Accepted solution]

Can you give more details?

Regards,

Tomasz

[ahaahaaha]

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

[Anonymous]

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]

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

[Anonymous]

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]

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 :

choose what type of measures you want to have  :

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

[Anonymous]

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]

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.

[Anonymous]

Awesome!! That worked perfectly!!!

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