So what is happening with your calculation is the difference from previous is returning a negative value so all will be labelled "down" see below:
To show the right value simply wrap all the function around brackets and stick in a minus:
Try to always see the numbers of the different steps in your calculation
Hope this helped!
Thanks, Sasha! But is there a way to display the information on a readable format?
It is not easy to read, nor to graph. Any suggestions?
I had a different take on this:
- I'm guessing that the up/down/same is supposed to be on the % of total, not the number of respondents (because the # of respondents is lower for every single value). In that case the Up/Down/Same calculation needs to change.
- The % of total calculation requires using Hide to hide the dissatisfied results. I've found this to be problematic for maintenance of visualizations because there's no obvious indicator that data has been hidden and that it makes it harder for people who are looking at the viz later to figure out what is going on. There are two common workarounds: one is to use a table calculation filter, the other is to use a record-level evaluation embedded in an aggregation. I used the latter in this case because it makes other parts easier.
- The existing Nr. of Responses measure uses a FIXED level of detail expression with COUNTD(). COUNTD() is slower than most other aggregations and the FIXED LOD is going to force an aggregation to be made. Given the following three factors that I could see in the data and the view: a) a 1:1 relationship between Email & Respondent ID; b) that there is only 1 record per Respondent ID and question/quarter and c) the viz Level of Detail includes question & quarter as dimensions then we can then get the % of total results as a regular aggregate that is basically a modified Excel SUMIF(). Here are the three calculations:
# of Responses jtd is IF [Answer (group)] != 'N/A' THEN 1 ELSE 0 END
# of Satisfied jtd is IF [Answer (group)] = 'Satisfied & Very Satisfied' THEN 1 ELSE 0 END
% of Total Sat is now SUM([# of Satisfied jtd]) / SUM([# of Responses jtd])
This no longer requires putting Answer (group) onto Filters to remove the N/A's nor putting Answer (group) into the viz and hiding the dissatisfied group, here's a workout view:
Then the up/down/same Latest % of Total calculation has the following formula with a compute using on the FY-Qtr:
IF LOOKUP([Diff from Prior % of Total Sat],LAST()) < 0 THEN
ELSEIF LOOKUP([Diff from Prior % of Total Sat],LAST()) = 0 THEN
ELSEIF LOOKUP([Diff from Prior % of Total Sat],LAST()) > 0 THEN
The LOOKUP([Diff from Prior % of Total Sat],LAST()) returns the latest value of the results and we can use it to generate this view:
Or if we don't need to show the exact value then we can do an even more compact layout like this where I used a simplified up/down/same calculation:
v10.1 workbook is attached.
UPDOWNSAME_TEST jtd.twbx 1.4 MB
This is great Jonathan. Thanks so much.
One quick question, how do you get the arrow picture. When I opened the workbook, it gives me a square instead.
One more thing … how would you go about graphing this as bar charts? (instead of a table)?
Virginie D. Bocard
ITSCS – Program, Initiatives & Analytics Team
Respectful Workplace Advisor for ITS
T +1 (202) 473-9665
1818 H Street, NW; Washington, DC, 20433
Questions on RWA’s role? Click for more information<http://spcapps.worldbank.org/sites/IJS/SitePages/RespectfulWorkAdvisor.aspx>
I'm guessing that the squares you are getting are because I created mine on the Mac and you're on Windows. You can use the Character Map app on Windows to insert arrows and other special characters.
As for making a bar chart can you provide a mockup of what you are looking for?
Oh got it for the arrow code. That makes sense.
As for the graphs, this is what I’ve been doing manually in PowerPoint and Excel (see screenshot attached).
Thanks for all the help. This is greatly appreciated.
image001.jpg 38.6 KB
Can you explain how the numbers for the arrows come about? I don’t see how they relate to the bars.
Sent from my iPhone
The arrows come from the difference from previous year. So basically it is your calculation of the difference between FY18Q3 vs FY18Q2.
I hope it clarifies.