3 Replies Latest reply on Jun 30, 2016 9:06 AM by Simon Runc

    Using a User's min/max date to fetch a Rank (avoid the dreaded aggregated/non-aggregated error OR LOD cannot use Table Calcs)

    Jim Thompson

      My brain hurts... The fact you cannot use a Table Calc in a LOD experession has thwarted me and I need the gurus to unravel the rope I have mentally tied myself in!


      In the included (contrived) workbook is a Ranking Line Chart showing ranks at a start week through to an ending week for Santa's helpers indicating their pedometer steps... (I did this since I figured it would take until Christmas for me to figure this one out...)


      My 'kludged' start week (calculated is a LOD expression since apparently I need an un-aggregated value to do my comparison- perhaps the root of my problem?) [Week Earliest- (lod)] & [Week Latest- (lod)] works FINE when ALL OF THE DATA IS USED by the User on the Viz, but my LOD calc for the start week goes out the window once the User selects a smaller range of weeks than all the data.


      As such, the Percent Change table is only accurate when the full range is displayed- and I know why since the { FIXED : MIN([Week Number])} has a scope of all the data- and not the view the User has selected... but I cannot for the life of me figure out another way to create an un-aggregated start value to use in the percentage change calculations... Aaarrgh!


      Opening the enclosed workbook and clicking on the Percent Change's top entry in the table (Rudolph) works great: you can see that Rudolph is slacking off... But, as User's are want... if you change the Week Number Range to 18 through 20 you'll see that it's Blitzen that's slacking off during that period... and my Percent Change Table remains at the scope of the entire data and does not change, of course...


      This type of analysis is VERY powerful when you've got thousands of Ranks happening-- but you really need the Percent Change to click on in order to find the guys that are tanking...


      Any help appreciated!