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: 
jlakehivewyre
Contributor III
Contributor III

Show Total and Yesterday only in KPI chart dynamically

Hello all, first time poster (fairly new to Qlik, but very quick learner).

I've got a set of data that goes back till beginning of 2017, and I update it daily through previous day.  I'd like to use a KPI chart to show the TOTAL Revenue for the selected time, as well as show Yesterday's revenue dynamically (ideally this wouldn't change if you selected various dates through the standard date/month chooser. 

Data Columns in question are "Day" and "Revenue (USD)"

Any help would be appreciated - Thank you

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Ok, then sum([Revenue (USD)]) for the selected time and for yesterday:

sum({<[Day]={"$(=Date(max({1}[Day])-1 ,'MM/DD/YYYY'))"}>} [Revenue (USD)])


or create a variable vYesterday as =Date(max({1}[Day])-1 ,'MM/DD/YYYY') and use the variable in the expression

sum({<[Day]={'$(vYesterday)'}>} [Revenue (USD)])


talk is cheap, supply exceeds demand

View solution in original post

8 Replies
Gysbert_Wassenaar

What does the field "Day" contain? Dates or date time values or day numbers? Some example data would be useful.


talk is cheap, supply exceeds demand
jlakehivewyre
Contributor III
Contributor III
Author

The "Day" contains dates in the following format:

MM/DD/YYYY

The Revenue (USD) contains numerical values that are ultimately dollars $X,XXX.00

Thanks

adj29block
Contributor III
Contributor III

Bonjour,

Look if that answers the question

Gysbert_Wassenaar

Ok, then sum([Revenue (USD)]) for the selected time and for yesterday:

sum({<[Day]={"$(=Date(max({1}[Day])-1 ,'MM/DD/YYYY'))"}>} [Revenue (USD)])


or create a variable vYesterday as =Date(max({1}[Day])-1 ,'MM/DD/YYYY') and use the variable in the expression

sum({<[Day]={'$(vYesterday)'}>} [Revenue (USD)])


talk is cheap, supply exceeds demand
jlakehivewyre
Contributor III
Contributor III
Author

Thank you Gysbert,

I have tried both of you options.  First I created the variable (see screenshot); and used this expression:

sum({<[Day]={'$(vYesterday)'}>} [Revenue (USD)])

The output is just $0.00

Is there something else I can share/show that might aid?  Perhaps I have something in the wrong format?Variable.jpg

jlakehivewyre
Contributor III
Contributor III
Author

Hello Jean-Baptiste,

I will make a copy of my app and try this!

dwforest
Specialist II
Specialist II

Depending on the content of day, is it just a date? a Day, like 28, or Date Time?

Even if it appears as a mm/dd/yyyy date, I have found Qlik to behave strangely with Dates in Set Expressions, it works best it both values equate to the number.

Since we can't use a formula for the left hand side we do this:

Assume you have a key field of ID.

sum({<ID = {"=(Date(Day,'mm/dd/yyyy')='$(vYesterday)')"}>} [Revenue (USD)])


might need to play with formatting options on either side to the resulting expression to evaluate correctly (numbers on both sides or equivalent date formats).

jlakehivewyre
Contributor III
Contributor III
Author

Okay - I ended up going back to the original data set, and changed the format of the "date" from Excel's "date" format, to just "general" - then used the Qlik data set to adjust the output to the correct date (MMDDYYYY) and now it works.  Though, I had to remove the -1 from the Variable; cause it was giving me the day before yesterday.  This is because my data is only loaded through yesterday, so it's factoring in today as well and was going back one day too far.

Fixing this now I am getting the expected output results.  Thanks so much for everyone's help!

Final Variable: =Date(max({1}[Day]) ,'MM/DD/YYYY')

Final Expression: sum({<[Day]={'$(vYesterday)'}>} [Revenue (USD)])