Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
Can you give more details?
Regards,
Tomasz
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
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
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
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.
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 :
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:
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)
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.
Awesome!! That worked perfectly!!!
Thank you so much Michael for the help!!!!!