Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

jlakehivewyre
New 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

Tags (1)
1 Solution

Accepted Solutions

Re: Show Total and Yesterday only in KPI chart dynamically

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
8 Replies

Re: Show Total and Yesterday only in KPI chart dynamically

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
New Contributor III

Re: Show Total and Yesterday only in KPI chart dynamically

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
New Contributor III

Re: Show Total and Yesterday only in KPI chart dynamically

Bonjour,

Look if that answers the question

Re: Show Total and Yesterday only in KPI chart dynamically

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
New Contributor III

Re: Show Total and Yesterday only in KPI chart dynamically

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
New Contributor III

Re: Show Total and Yesterday only in KPI chart dynamically

Hello Jean-Baptiste,

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

dwforest
Valued Contributor

Re: Show Total and Yesterday only in KPI chart dynamically

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
New Contributor III

Re: Show Total and Yesterday only in KPI chart dynamically

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)])

Community Browser