tableau - Aggregate by most recent not-null value -


i have dataset following columns [ product_id, country_id, date, number_of_installs, cumulative_installs_last_30_days ]

i have no problem applying standard measures find sum, max or average number_of_installs within 3 dimensions (product_id, country_id, date(aggregated month or week)). however, have not been able aggregate cumulative_installs_last_30_days because variable cumulative, need return “most recent value” , tableau not have option built-in aggregation functions.

how create calculated field enables addicional column in aggregated dataset recent not-null value of cumulativeinstalls_last_30_days within dimensions product_id, country_id , date(aggregated month or week)?

here's dirty solution.

in comments, noted wanted 30 days dynamic, accomplish that, create parameter, make integer, select range, , allow integer on zero. i'll call [number of days].

then create calculated field:

total(sum(iif(datediff("day", [date], today()) < [number of days], [number of installs], null)))

i know that's ridonk, i'll break down, inside out.

datediff("day", [date], today())

that calculates difference in days between today , date in given row.

iif(datediff("day", [date], today()) < [number of days], [number of installs], null)

that checks if difference less number of days selected. if is, statement equal number of installs. if it's not, it's null. result, if sum of these values, number of installs in last [number of days] days.

with in mind, sum() rows. total() performs sum on every database row contributes partition.

note if database has dates after today(), you'll need add condition iif() statement make sure aren't included.

you mentioned want able aggregate number of installs month. that's easier. toss month([date]) dashboard, sum([number of installs]), , tableau knock out you.


-

Comments

Post a Comment

Popular posts from this blog

c# - Binding a comma separated list to a List<int> in asp.net web api -

the default binder in web api expecst http://url.com/webapi/report/?pageids=3243&pageids=2365 to bind public ihttpactionresult report(list<int> pageids){ // exciting webapi code} i wish bind http://url.com/webapi/report/?pageids=3243,2365 as running out of space in url get . i have created class public class commaseparatedmodelbinder : system.web.http.modelbinding.imodelbinder { public bool bindmodel(httpactioncontext actioncontext, modelbindingcontext bindingcontext) { //binding in here } } and registered in webapiconfig.cs var provider = new simplemodelbinderprovider( typeof(list<int>), new commaseparatedmodelbinder()); config.services.insert(typeof(modelbinderprovider), 0, provider); i have altered method signature use model binder so public ihttpactionresult report( [modelbinder] list<int> pageids){ // exciting webapi code} however break point in binder not being
Read more

Delphi 7 and decode UTF-8 base64 -

in delphi 7, have widestring encoded base64(that received web service widestring result) : pd94bwwgdmvyc2lvbj0ims4wij8+dqo8c3ryaw5nptiq2lpyqjwvc3ryaw5npg== when decoded it, result not utf-8: <?xml version="1.0"?> <string>طھط³طھ</string> but when decoded base64decode.org, result true : <?xml version="1.0"?> <string>تست</string> i have use encddecd unit decodestring function. the problem have using decodestring . function, in delphi 7, treats decoded binary data being ansi encoded. , problem text utf-8 encoded. to continue encddecd unit have couple of options. can switch decodestream . instance, code produce utf-8 encoded text file data: {$apptype console} uses classes, encddecd; const data = 'pd94bwwgdmvyc2lvbj0ims4wij8+dqo8c3ryaw5nptiq2lpyqjwvc3ryaw5npg=='; var input: tstringstream; output: tfilestream; begin input := tstringstream.create(data); try output := tfilestream.
Read more

html - Is there any way to exclude a single element from the style? (Bootstrap) -

so, have img logo, overlaid on div styled background-image: url("...") , , both elements inside 2 div classes .to-color , .to-overlay respectively, black transparent overlay. is there way exclude logo style given these 2 classes? here's jsfiddle example: link html , css example: - html - <div class="to-color"> <div class="to-overlay"> <div class="respo"> <div class="row"> <div class="col-xs-4 col-xs-offset-4 text-center"> <img src="http://ansonalex.com/wp-content/uploads/2011/06/google-logo-768x260.png" alt="..." class="img-responsive"> </div> </div> </div> </div> </div> - css - .to-color { background-color: #000; } .to-overlay { opacity : 0.5; } .respo { background-image: url("https://source.unsplash.com/category/objects/1600x900"); background-position: center center;
Read more