æ¯é±å·çããããšã«äžåã³ãããããŸããããWorkout Wednesdayã«é¢ããããã°ãæžãå§ããŸãã
ç®çã¯Workout Wednesdayã§çŽ¹ä»ãããŠããTableauãã¯ããã¯ã®å匷ã§ãã
Workout Wednesdayã¯å®å šåçŸãç®çãšããŠããŸãããåã¯ãããŸã§ãããŸããã
æžåŒèšå®ãããŒã«ãããã¯é©åœã§ãããã¯ããã¯ã®å匷ãç®çãªã®ã§ã
ãã®ããã°ã«äœ¿çšãããTableauã¯ãŒã¯ããã¯ã¯ä»¥äžããããŠã³ããŒãã§ããŸãã
Workout Wednesdayã«ã€ããŠïŒhttp://www.workout-wednesday.com/
Week 46ã®ããŒãžïŒhttp://www.workout-wednesday.com/2018-week-46/
ãä»åã®ãé¡ã
å ã ã¯Andyã®Makeover Monday W45ã®äœåã§ãã
åŠãã ããšïŒ
ã»è¡šèšç®ãç¹ã«LOOKUP()ã®åŸ©ç¿ã
ã»Line Chartã®å§ç¹ãšçµç¹ã«ãããã眮ãæ¹æ³ã
ã»Reference Lineã䜿ã£ãŠã軞ã®ç¯å²ããèªåãã«ããªããã軞ç¯å²ã«ããšããããããããã
以äžãé ã«äœãæ¹ã解説ããŠãããŸãã
ããŒã¿ã»ããã¯ãã¡ãããããŠã³ããŒãã§ããŸãã
ãããŒã¿ã®æŽåœ¢ãšåºæ¬Vizäœæã
æŽåœ¢ãšããã»ã©å€§è¢è£ã§ã¯ãªãã§ãããå ããŒã¿ã¯æšªã«é·ãããããŒã¿ã§ããã
å°ã 䜿ãã«ããã®ã§ããŸãã¯ããããããŸããAge 0ããAge100ãŸã§éžæããŸãã
ããããã®ãã£ãŒã«ãåããAge Labelããããããã®ã¡ãžã£ãŒåããPopulationããšã§ãããŠãããŸãã
次ã«ãå ã®Vizã«æ²¿ã£ãŠãOlder AdultsããšãChildrenãã欲ããã®ã§ãå ã»ã©ã®ãAge LabelãããäœæããŸãã
Children/OlderAdults
IF INT(SPLIT([Age Label]," ",-1)) <= 17 THEN "Children" ELSEIF INT(SPLIT([Age Label]," ",-1)) >= 65 THEN "Older Adults" END
ãããŠä»¥äžã®ããã«é 眮ããŸããChildren/OlderAdultsã®NULLã¯é衚瀺ã«ããŠãããŸãã
ïŒé衚瀺ã®ããæ¹ã¯ãå¡äŸã®NULLãå³ã¯ãªãã¯âé衚瀺ã§ãïŒ
次ã«ã2ã€ã®Line Chartã亀ããYEARãæ±ããããã«ãããã€ãã®äœæ¥ãããŸãã
(ä»åã®Workout Wednesdayã§ã¯LODèšç®ã䜿ããªãããšããçžãã§ããããããå°å³ã«èŸãâŠ)
ã2ã€ã®çŽç·ã亀ããããšèããŠãæåã«ã€ã¡ãŒãžããã®ã¯é£ç«æ¹çšåŒã®ãããªã2ã€ã®çŽç·ã®ãy軞ã®å€ãçããx軞ã®å€ãæ±ããã°è¯ãããšãããããªããšã§ããããã
ããããä»åã®å Žã¯YEARã¯äžé£ç¶ãªæŽæ°ãªã®ã§ãäžã®ããžãã¯ã¯éçšããŸãã(æ±ããYEARãå°æ°ã«ãªãããã®ã§)ã
ãããã¯ãChildrenã®PopulationããOlder Adultsã®Populationãè¶ ããYEARããšãã解éã§ããä»åã®å Žåã¯æå³ãéããŸãã
åã®å Žåã¯ãããæç¹ã®YEARã§ã®Populationã®å€§å°é¢ä¿ãšããã®1幎åã§ã®å€§å°é¢ä¿ãé転ããYEARããšããããã«å®çŸ©ããŸããã
åé¡ã¯ãåã®å®çŸ©ã®å Žåãããšãã°ãã©ã®ããã«ããŠã"Children"ã®"1幎åã®Population"ããâOlder Adultsâã®âä»å¹Žã®Populationâã«äžããŠãããããšããããšãåé¡ãšãªããŸãã
ã©ãããããšããšãããšãåºç€ãšããäžã®Vizã§ã¯ãåããŒã¿ç¹ã¯ãChildren/OlderAdultããšãYEARãã§åºåãããŠããŸãããã®ç¶æ³ã§ãããããŒã¿ç¹ã«ãåºåãããŸããã ããŒã¿ã®æ å ±ãäžããŠãããªããšãããªãããšããããšã§ãã
ãTable Calcã䜿ãåãã
ããããå Žåã«LODã§é 匵ããªãããªãã§ãããä»åã¯Table Calcã®ç·Žç¿ã§ãã
ãŸãæåã«ãOlder Adultsã®ããŒã¿ç¹ã«Childrenã®ããŒã¿ãæž¡ãæ¹æ³ãã«ã€ããŠè§£èª¬ããŸãã
çµè«ãšããŠã¯ãLOOKUP()ã䜿ããŸãã
Children Population %
LOOKUP( IF ATTR([Children/OlderAdults]) = "Children" THEN SUM([Population]) / TOTAL(SUM([Population])) END )
Older Adults Population %
LOOKUP( IF ATTR([Children/OlderAdults]) = "Older Adults" THEN SUM([Population]) / TOTAL(SUM([Population])) END )
LOOKUP()ãããããäœãã«ã€ããŠã¯ãGoogleã§èª¿ã¹ããšãããªãã¡ã¬ã³ã¹ãããããããæ§åãªã®ã§ããã¡ãã«ãä»»ãããŸãã
èŠã¯ãChildren/OlderAdultsã«ãããã察å¿ããåºæºãèšããŠãããã®ã¡ã«(Childrenãããã¯Older Adultsãåç §ããããã«èšå®ããŠãããã®ã¡ã«)ãIFæåããŠãããŠããããè¡šèšç®ã§ããŒã¿ã®åºåãããŸãããããã«ããŠããããããšããæãã§ã(ãã£ãšãå€å)ã
ãããŸã§çµæãèŠãŠã¿ããšã以äžã®ããã«ãªããŸããç°¡åã®ããã«Originã¯TotalãSexã¯Bothã®ããã«ã®ã¿ãæãåºããŠããŸãã
ãã£ã¡ã³ã·ã§ã³ã§Children/OlderAdultsãåããŠããäžæ¹ã§ããããããç°ãªããããã¯ã«ã察å¿ããæ°åãå ¥ã£ãŠããããšãããããŸãã
ã§ã¯ã1幎åã®å€ãæã£ãŠããããšããããšã¯ã©ãããã°ããã§ããããã
èãæ¹ãšããŠã¯ãChildren/OlderAdultsã䜿çšããŠãLOOKUP()ã§æ±ããå€ããããã«âYEARã䜿çšããLOOKUP()"ã䜿çšãããŸããèšç®åŒãèŠãŠé ããæ¹ãæ©ãã§ãã
Children Population % -1 YEAR
LOOKUP([Children Population %],-1)
Older Adults Population % -1 YEAR
LOOKUP([Older Adults Population %],-1)
2016幎ã¯YEARã®éå§ãªã®ã§ãPreviousã®å€ããããŸããã
ãã以å€ã§ã¯ãäŸãã°2017幎ã§ã¯ã2016幎ã®å€ãåç §ããŠããŸãã
ãã®ããã«ãLOOKUP()ã䜿ãåãããšã«ãããã©ã®ããã«ããŠã"Children"ã®"1幎åã®Population"ããâOlder Adultsâã®âä»å¹Žã®Populationâã«äžããŠããããåé¡ã解決ããŸããã
ãšããããšã§ãäžèšã®åŒã«ãã£ãŠäžä»£é人å£ã®é転ãèµ·ãã幎ãæ±ããããŸãã
(å ã»ã©ã®Previousã¯äœ¿ã£ãŠããŸããããæ¬è³ªçã«ã¯åãåŒã§ãã)
(äœè«ã§ããã圢ãåããããã£ãã®ã§LOOKUP(,0)䜿ã£ãŠãŸããããããããªãæ°ãããŸã
Crossing Year
IF LOOKUP([Children Population %]-[Older Adults Population %],-1) * ([Children Population %]-[Older Adults Population %]) <= 0 THEN MIN([Year]) END
æ£ããè¡šèšç®ã®ç·šéãããŠããã°ããã®Crossing YearãReference Lineã«çšããããšã§ãäžèšã®VizãŸã§äœæã§ããŠããã¯ãã§ãã
ãLine Chartã®å§ç¹ã亀ç¹ãçµç¹ã«ããããã€ããã
次ã«ãããããã€ããäœæ¥ãããŸãã
Line Chartå§ç¹ãšçµç¹ã ãã«é¢ããŠèšãã°ã以äžãäžçªæ©ãã§ãã
ãã®æ¹æ³ã®ãã¡ãªãããæãããšãããããããã ãã®ãµã€ãºå€æŽãã§ããªãïŒã¯ãã§ãããç¥ããªãã ããããããŸããïŒããšã§ãã
ãšããããšã§ãã©ãã亀ç¹ã«ããã眮ããªããå§ç¹ãšçµç¹ãåãæ¹æ³ã§çœ®ããŠãããããšæããŸãã
以äžã®èšç®åŒãçšæããŸãã
Year Label
CASE MIN([Year]) WHEN WINDOW_MIN(MIN([Year])) THEN "MIN" WHEN WINDOW_MAX(MIN([Year])) THEN "MAX" WHEN [Crossing Year] THEN "CROSS" END
ãã®Year LabelçšããŠãäžå³ã®ããã«ã»ããã£ã³ã°ããŸãã
Populationã®åèšã«å¯Ÿããå²åãã³ããŒãã2é軞ïŒè»žã®åæãããŸãã2ã€ç®ã®Populationã¯åã«ããŸãã
ãReference Lineã䜿ã£ãŠè»žç¯å²ã«ããšããã
æåŸã«ãã¡ãã£ãšã©ãã«ã窮å±ãªã®ã§ã軞ç¯å²ãåºãã«ããŸãã
ããã§è»žã®ç·šéâ軞ç¯å²ã®åºå®ãã¯ããŸãããReference Lineã䜿ããŸãã
ãã®ããã«ä»¥äžã®èšç®åŒãçšæããŸãã ããã§[OFFSET]ã¯ãã©ã¡ãŒã¿ã§ãã
MAX Year Offset
WINDOW_MAX(MIN([Year])) + [OFFSET]
MIN Year Offset
WINDOW_MIN(MIN([Year])) - [OFFSET]
ãããããReference Lineã«å ¥ããŠããããã©ã¡ãŒã¿ã§å¥œããªäœçœãäžããŠãã ããã
æ®ããã¡ãã£ãšã ããã©ãŒãããã觊ãã°ã以äžã®ãããªåœ¢ã«ãªããŸãã
ãã€ããªããçç¥ããåæãããã®ã§ãåãããªãç¹ãªã©ããã°ãæ°è»œã«ãé£çµ¡ãã ããã