Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)])
What does the field "Day" contain? Dates or date time values or day numbers? Some example data would be useful.
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
Bonjour,
Look if that answers the question
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)])
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?
Hello Jean-Baptiste,
I will make a copy of my app and try this!
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).
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)])