Forum Discussion

Bokchoy's avatar
Bokchoy
Icon for Helper II rankHelper II
4 years ago
Solved

look up value within temporary groupby table

Hi all,   I have a temp table that sums up total_charge, grouped by single_date. Now i want to return the date where the highest "sum all charge" was hit. im having troubles with referencing the...
Need Help
Show and Tell
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    Bokchoy 

    is this what you want?

    maxdate = 
VAR tbl=SUMMARIZE(sample_jobs,sample_jobs[job_date],"charge",sum(sample_jobs[total_charge]))
VAR _max= maxx(tbl,[charge])
return maxx(FILTER(tbl,[charge]=_max),'sample_jobs'[job_date])

month = FORMAT([maxdate],"mmm")

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
4 years ago

Bokchoy 

is this what you want?

maxdate = 
VAR tbl=SUMMARIZE(sample_jobs,sample_jobs[job_date],"charge",sum(sample_jobs[total_charge]))
VAR _max= maxx(tbl,[charge])
return maxx(FILTER(tbl,[charge]=_max),'sample_jobs'[job_date])

month = FORMAT([maxdate],"mmm")

  • Bokchoy's avatar
    Bokchoy
    Icon for Helper II rankHelper II
    4 years ago

    Hi ryan,

    thanks for helping!

    i was able to get what i needed from your code, though i used the Groupby function instead of summarize. The return maxx line was that i was looking for. 

    again thanks heaps.

    Record Daily date = 

#maxdate = 
#VAR tbl=SUMMARIZE(jobs,Jobs[job_date],"charge",sum(jobs[total_charge]))
#VAR _max= maxx(tbl,[charge])
#return maxx(FILTER(tbl,[charge]=_max),Jobs[job_date])

var temp1 = GROUPBY(Jobs, Jobs[job_date] , "Sum all Charge", SUMX(CURRENTGROUP(), Jobs[total_charge]))
var High_Daily = MAXX(temp1, [Sum all Charge])
var date_ = MAXX(FILTER(temp1, [Sum all Charge] = High_Daily), Jobs[job_date])

return date_




    • ryan_mayu's avatar
      ryan_mayu
      Icon for Super User rankSuper User
      4 years ago

      you are welcome