1. 

   haigda, Member

   I am trying to define a background cell colour as defined by the value of another column. Basically I am trying to make the colour of the Percent Column (dbr.calc) change depending on its value or better still the value of ThisYear and LastYear. But because ThisYear and LastYear are themselves 'sums' I don't get the figures I expect - it seems to be the last records of the summary rather than the totals if you see what I mean.

   Is there a better way to pass the percentage to the f_color_scale10 function? I would prefer to pass the ThisYear and LastYear cells as I can then trap divide by zero and other anomalies.

   select 'dbr.hidecolumn', 'tvlYear';
   
   select 'dbr.hidecolumn', 'tvlMonth';
   
   select 'dbr.hidecolumn', 'cellstyle';
   
   select 'dbr.cellstyle', 'percent', 'cellstyle';
   
   select 'dbr.sum', 'ThisYear', 'LastYear';
   
   select 'dbr.calc', 'percent', '([ThisYear]/[LastYear])';
   
   select tvlYear,tvlMonth,monthname(concat(tvlYear,"-",tvlMonth,"-01")) as Month,
   
   sum(case when tvlAnalysis in ("GD", "GE", "GI", "GS", "GW", "GO")then thisyear else 0 end) as 'ThisYear',
   
   sum(case when tvlAnalysis in ("GD", "GE", "GI", "GS", "GW", "GO")then lastyear else 0 end) as 'LastYear',
   
   null as '%[percent]',
   
   concat( 'background-color:#',f_color_scale10( ThisYear/lastYear )) as 'cellstyle'
   
   from  dt_travel.vdt_combo_pax
   
   where (tvlYear = 2015 and tvlMonth > 10) or (tvlYear = 2016 and tvlMonth < 11)
   
   Group by tvlYear, tvlMonth;
   
   
2. 

   myDBR Team, Key Master

   Hi,
   sometimes it is easier to use subqueries in the from-clause so you can use the calculated columns directly and you do not need to replicate (sometimes complicated) calculations.

   select 'dbr.hidecolumn', 'tvlYear';
   
   select 'dbr.hidecolumn', 'tvlMonth';
   
   select 'dbr.hidecolumn', 'cellstyle';
   
   select 'dbr.cellstyle', 'percent', 'cellstyle';
   
   select 'dbr.sum', 'ThisYear', 'LastYear';
   
   select
   
     tvlYear,
   
     tvlMonth,
   
     MonthName,
   
     ThisYear,
   
     LastYear,
   
     ThisYear/LastYear as '%[percent]',
   
     concat( 'background-color:#',f_color_scale10( ThisYear/lastYear )) as 'cellstyle'
   
   from (
   
       select
   
         tvlYear,
   
         tvlMonth,
   
         monthname(concat(tvlYear,"-",tvlMonth,"-01")) as MonthName,
   
         sum(case when tvlAnalysis in ("GD", "GE", "GI", "GS", "GW", "GO")then thisyear else 0 end) as 'ThisYear',
   
         sum(case when tvlAnalysis in ("GD", "GE", "GI", "GS", "GW", "GO")then lastyear else 0 end) as 'LastYear'
   
     from  dt_travel.vdt_combo_pax
   
     where (tvlYear = 2015 and tvlMonth > 10) or (tvlYear = 2016 and tvlMonth < 11)
   
     group by tvlYear, tvlMonth
   
   ) as q;

   --
   myDBR Team
3. 

   haigda, Member

   Thank you

   Works brilliantly

   Cheers,
   David

Reply

You must log in to post.