#Output0 debatiendo

OK, explanations for the last two views and the dashboard.

Highway Spending per Capita for Selected Areas

This builds on a lot of the things we did in the previous view, but has one extra twist which I'll explain first before the detailed instructions.

The table calculation for the field [Amount Spent per Capita] uses the expression:

[Selected Amount Spent] / [Selected Population]

where both of those are themselves table calculation, but with different addressing: [Selected Amount Spent] is calculated over all roadnames whereas [Selected Population] is calculated over all dissemination blocks. This means we can't just use the 'Compute Using' menu option for [Amount Spent per Capita] to set the addressing, because that only allows us to pick one field, so only one of the two component calculations would be correct. Instead we need to use the 'Edit Table Calculation' option which gives the option to set the addressing individually for each component calculation:

The dialog has a drop-down list allowing you to choose each component calculation in turn.

At first sight you would think that for 'Selected Population' you should just check [dbuid] (meaning the calculation would be performed over all [dbuid] values for each combination of [Highway Roadname] and [Population Center]) and for [Selected Amount Spent] you should check [Highway Roadname] (meaning the calculation would be performed over all [Highway Roadname] values for each combination of [dbuid] and [Population Center]). But that results in [Amount Spent per Capita] being null. This screenshot of the 'show data' window hopefully explain what is happening here: [Selected Population] is always null for highway rows and [Selected Amount Spent] is null for all dissemination block rows - so [Amount Spent per Capita] is always null (because one or other of the two fields used to calculate it is always null).

So actually what we need to do is perform both calculations over all values of both [Highway Roadname] and [dbuid] for each [Population Center], by setting both like this:

That means that both components calculations and hence the [Amount Spent per Capita] are evaluated correctly for every row, as shown here:

We will again want to filter the view down to a single row, using the [First Row?] field, as we did previously.

Step by step instructions.

1) Create a new sheet and call it 'Highway Spending per Capita for Selected Areas'.

2) Drag [Population Center] from datasource 'Pop Centre Dissemination Block Highway' onto the Rows shelf.

3) Switch to sheet 'Dissemination Blocks and Highways around Population Centers' and for both the population and population center filters select 'Apply to Worksheets' and select 'Highway Spending per Capita for Selected Areas' to apply those filters to our new sheet.

4) Switch back to 'Highway Spending per Capita for Selected Areas' and drag [dbuid] and [Highway Roadname] onto the detail shelf.

5) Drag [Amount Spent per Capita] onto the Columns shelf, and set the addressing using the 'Edit Table Calculation' option from the drop-down menu on the field. Select both [dbuid] and [Highway Roadname] for each of the two component calculated fields, as per the explanation above.

6) Drag [Is First?] onto the filter shelf and select 'True' when prompted (the reason it only offers 'True' is because with the default addressing there is only one row per partition - so that row is always the first row in its partition). Now use 'Edit Table Calculation' to set the addressing to use both [dbuid] and [Highway Roadname] as above. Finally select 'True' when offered 'True' or 'False' to filter to just the first row.

7) From the Label shelf select 'Show mark labels'.

8) Optionally format the [Amount Spent per Capita] field and axis to use currency format.

Your view should now look like this for Saskatoon.

Highway Spending by Population Center and Route

Last sheet. This one is very straightforward, no new techniques or concepts, so just brief step-by-step instructions should do.

1) Create a new sheet and call it 'Highway Spending by Population Center and Route'.

2) Drag [Population Center] from datasource 'Pop Centre Dissemination Block Highway' onto the Rows shelf.

3) Switch to sheet 'Dissemination Blocks and Highways around Population Centers' and for both the population and population center filters select 'Apply to Worksheets' and select 'Highway Spending by Population Center and Route' to apply those filters to our new sheet.

4) Switch back to 'Highway Spending by Population Center and Route', drag [Highway Roadname] onto the Detail shelf and [Highway Route Number1] onto the Rows shelf.

5) Drag [Selected Amount Spent] onto the Columns shelf, and set the addressing using the 'Compute Using' option from the drop-down menu on the field. Select [Highway Roadname].

6) Drag [Is First?] onto the filter shelf and select 'True' when prompted. Now use the 'Compute Using' menu option to set the addressing to use [Highway Roadname] as above. Finally select 'True' when offered 'True' or 'False'.

7) From the Label shelf select 'Show mark labels'.

8) Optionally format the [Selected Amount Spent] field and axis to use currency format.

Your view should now look like this for Saskatoon.

One thing that is worth pointing out is that the calculated field [Selected Amount Spent] is the same field as we used on sheet 'Population and Highway Spending for Selected Areas', but gives a different answer this time because we have the route number dimension as well as population center on this sheet, so the result is broken down further.

Finally create a dashboard, drag all the sheets on and arrange as you see fit.

The filters should all just work on all sheets because of the way that we applied the filters from the first sheet to all of the other sheets.

You will probably want to edit the titles and alias some of the entries in the legends and do various other bits of tidy up. Here's what mine looks like:

For extra bonus points you can do things like adding highlight actions to the dashboard so that you can click on route numbers in the bar chart and highlight the routes on the map:

I noticed that you had created extracts for both the census CSV file and the expenditure spreadsheet. That is definitely a good thing to do - after seeing that I've done it for mine and although I haven't timed it the workbook seems quicker - it is certainly really responsive. One thing you could do is recreate the census extract with a filter on province, so that you only include dissemination blocks for Saskatchewan. I don't think that will make a measurable difference to performance, but it saves a bit of space - and it's a good habit to get into, I think - no point in carrying around lots of excess data that is unrelated to what you are doing.

So that's all there is to it.

It has certainly turned into a much bigger exercise than I was expecting and we have covered an awful lot of ground and lots of different Tableau concepts and techniques. It's actually been really useful for me - I've been right out of the Tableau world for a long time so it's been a great chance to refresh my knowledge and get up to date on all the changes that have been happening since I was last using it regularly.

As I say, I'm fully expecting some more questions (including working through the one you are stuck on at the moment). I'll get to those as and when I'm around.

Good luck!

@Joseph Crapanzano​ 

Hi, I have modified your python script:

import pandas as pd
⌗import tabpy
⌗from tableau_tools import prep_decimal, prep_int, prep_bool, prep_string, prep_date, prep_datetime
 
# Define duration_columns as a global variable
duration_columns = [
    'Step 1',
    'Step 2',
    'Step 3',
    'Step 4',
    'Step 5',
    'Step 6',
    'Step 7',
]
 
def moving_average(data, window=10, max_visits=150):
    # Filter rows based on Appointment Status
    filtered_data = data[(data['appt_status'] == 'CHECKED_OUT') | (data['appt_status'] == 'CHECKING_OUT')]
    # Group by Appointment Type ID and sort by Appointment DateTime of Service
    grouped_data = filtered_data.groupby('type_id')
 
    # Initialize moving average columns with NaN
    for duration_column in duration_columns:
        data[f"Moving Average {duration_column}"] = float('nan')
 
    for name, group in grouped_data:
        sorted_group = group.sort_values(by='datetime')
        sorted_group = sorted_group.head(max_visits)
        if len(sorted_group) >= window:
            for duration_column in duration_columns:
                if duration_column in sorted_group.columns:
                    sorted_group[f"Moving Average {duration_column}"] = sorted_group[duration_column].rolling(window=window).mean()
 
            # Update the moving average columns in the original dataset
            data.loc[sorted_group.index, [f"Moving Average {duration_column}" for duration_column in duration_columns]] = sorted_group[[f"Moving Average {duration_column}" for duration_column in duration_columns]]
 
    return data
 
def get_output_schema():
    return pd.DataFrame({
        **{f'Moving Average {name}': prep_decimal() for i, name in enumerate(['Step 1', 'Step 2', 'Step 3', 'Step 4', 'Step 5', 'Step 6', 'Step 7'], start=1)},
    })
 
def prep_script(input):
    # Convert input data to pandas DataFrame
    input_data = input
 
    # Call the moving_average function
    output = moving_average(input_data)
    print(output)
 
    # Convert the result back to a list of dicts
    #output = [row.to_dict() for _, row in result.iterrows()]
 
    # Get the output schema using the get_output_schema function
    #output_schema = get_output_schema()
 
    # Return output and schema
    return output#, output_schema

Now it is working, I think you can start from here.

If this post resolves the question, would you be so kind to "Select as Best"?. This will help other users find the same answer/resolution and help community keep track of answered questions. Thank you.

Regards,

Diego Martinez

Tableau Visionary and Forums Ambassador

@Mohammed Abdul Khader Sufi​ 

Thanks for you response.

But I need to verify both the fields. Last condition is not working. If code > 10 and street =null remove code and paste whatever the value coming from code to street.

Is it possible to write down the condition in below similar way.

{

"street": condition,

"code": condition

}

Regards

Niranjan K

We seem to be going round and round here. Let me say this...unless you externalize the dataweave script(s), you cannot comment XML code with Ctrl+Shift+/. That being said, the externalized dataweave expressions are within the same project...they are not anywhere outside of that project. By default, they are saved in src/main/resources. If writing dataweave expressions to a separate files is not an option, then my suggestion will not help in what you trying to do. IMO, externalizing the dataweave script has a couple of advantages: (1) Keeps my XML code clean i.e. it only contains xml and nothing else, so I dpn't have to deal with expressing non-xml content with CDATA element...that usually makes my code look ugly (2) If the dataweave expression needs to reusable, all I have to do is oint to the file where it is located rather than redefine it inline.

my 2 cents.

-gopal

Hi David!

Thanks for sharing!

Commenting to bring this thread to the top of the feed!

Cheers,

Byrne, Patrick