Sort

1. In the Data pane:

   1. Select daterange.
   2. Select product_namr.
   3. Select sum purchase_revenue.
   4. Select sum purchases.

2. In the Filters pane:

   1. Select daterange is (All) from Filters on this visual.
   2. Select Advanced filtering as the Filter type.
   3. Define the filter to Show items when the value is on or after 1/1/2023 And is before 2/1/2023.

3. In the Visualizations pane:

   1. Select to remove daterange from Columns.
   2. Drag Sum of purchase_revenue to the bottom of Column items.

4. In the report, select Sum of purchase_revenue to sort the table in descending order of purchase revenue.

   Your Power BI Desktop should look like below.

   

The query executed by Power BI Desktop using the BI extension is not including a sort statement. The lack of a sort statement implies that the sort is executed client side.

select "_"."product_name",
    "_"."a0",
    "_"."a1"
from
(
    select "rows"."product_name" as "product_name",
        sum("rows"."purchases") as "a0",
        sum("rows"."purchase_revenue") as "a1"
    from
    (
        select "_"."daterangeName",
            "_"."daterange",
            "_"."filterId",
            "_"."filterName",
            "_"."timestamp",
            "_"."affiliate_name",
            "_"."affiliate_url",
            "_"."commerce.order.priceTotal",
            "_"."customer_city",
            "_"."customer_region",
            "_"."daterangeday",
            "_"."daterangefifteenminute",
            "_"."daterangefiveminute",
            "_"."daterangehour",
            "_"."daterangeminute",
            "_"."daterangemonth",
            "_"."daterangequarter",
            "_"."daterangesecond",
            "_"."daterangethirtyminute",
            "_"."daterangeweek",
            "_"."daterangeyear",
            "_"."hitdatetime",
            "_"."page_name",
            "_"."page_url",
            "_"."product_category",
            "_"."product_name",
            "_"."product_short_review",
            "_"."product_subCategory",
            "_"."referrer_url",
            "_"."search_engine",
            "_"."search_keywords",
            "_"."store_city",
            "_"."store_name",
            "_"."store_region",
            "_"."store_type",
            "_"."timepartdayofmonth",
            "_"."timepartdayofweek",
            "_"."timepartdayofyear",
            "_"."timeparthourofday",
            "_"."timepartminuteofhour",
            "_"."timepartmonthofyear",
            "_"."timepartquarterofyear",
            "_"."timepartweekofyear",
            "_"."cm_session_end_rate_defaultmetric",
            "_"."cm_session_person_defaultmetric",
            "_"."cm_session_start_rate_defaultmetric",
            "_"."cm_timespent_person_defaultmetric",
            "_"."cm_timespent_session_defaultmetric",
            "_"."cm_product_name_count_distinct",
            "_"."ad_views",
            "_"."adobe_sessionends",
            "_"."adobe_sessionstarts",
            "_"."adobe_timespent",
            "_"."exchange_buybacks",
            "_"."exchange_cost",
            "_"."exchange_purchases",
            "_"."exchange_revenue",
            "_"."occurrences",
            "_"."page_views",
            "_"."product_quantity",
            "_"."product_reviews",
            "_"."product_views",
            "_"."purchase_revenue",
            "_"."purchases",
            "_"."visitors",
            "_"."visits"
        from "public"."cc_data_view" "_"
        where "_"."daterange" < date '2023-02-01' and "_"."daterange" >= date '2023-01-01'
    ) "rows"
    group by "product_name"
) "_"
where not "_"."a0" is null or not "_"."a1" is null
limit 1000001

1. Select the Sheet 1 tab at the bottom to switch from Data source. In the Sheet 1 view:

   1. Drag Daterange entry from the Tables list in the Filters shelf.

   2. In the Filter Field [Daterange] dialog, select Range of Dates and select Next >.

   3. In the Filter [Daterange] dialog, select Range of dates, and select 01/01/2023 - 1/2/2023. Select Apply and OK.

   4. Drag Product Name from the Tables list and drop the entry in the field next to Rows.

   5. Drag Purchases entry from the Tables list and drop the entry in the field next to Columns. The value changes to SUM(Purchases).

   6. Drag Purchase Revenue entry from the Tables list and drop the entry in the field next to Columns, next to SUM(Purchases). The value changes to SUM(Purchase Revenue).

   7. Select Text Table from Show Me.

   8. Select Fit Width from the Fit drop-down menu.

   9. Select the Purchase Revenue column header and sort the table on this column in descending order.

      Your Tableau Desktop should look like below.

      

The query executed by Tableau Desktop using the BI extension is not including a sort statement. The lack of this sort statement implies that the sort is executed client side.

SELECT CAST("cc_data_view"."product_name" AS TEXT) AS "product_name",
  SUM("cc_data_view"."occurrences") AS "sum:occurrences:ok",
  SUM("cc_data_view"."purchase_revenue") AS "sum:purchase_revenue:ok",
  SUM("cc_data_view"."purchases") AS "sum:purchases:ok"
FROM "public"."cc_data_view" "cc_data_view"
WHERE (("cc_data_view"."daterange" >= (DATE '2023-01-01')) AND ("cc_data_view"."daterange" <= (DATE '2023-02-01')))
GROUP BY 1

1. In the Explore interface of Looker, refresh your connection. Select Clear cache and refresh.

2. In the Explore interface of Looker, ensure you do have a clean setup. If not, select Remove fields and filters.

3. Select + Filter underneath Filters.

4. In the Add Filter dialog:

   1. Select ‣ Cc Data View
   2. From the list of fields, select ‣ Daterange Date then Daterange Date.
      

5. Specify the Cc Data View Daterange Date filter as is in range 2023/01/01 until (before) 2023/02/01.

6. From the ‣ Cc Data View section in the left rail, select Product Name.

7. From the ‣ Custom Fields section in the left rail:

   1. Select Custom Measure from the + Add drop-down menu.

   2. In the Create custom measure dialog:

      1. Select Purchase Revenue from the Field to measure drop-down menu.
      2. Select Sum from the Measure type drop-down menu.
      3. Enter a custom field name for Name. For example: Sum of Purchase Revenue.
      4. Select the Field details tab.
      5. Select Decimals from the Format drop-down menu and ensure 0 is entered in Decimals.
         
      6. Select Save.

8. Ensure you select ↓ (Descending, Sort Order: 1) on the Purchase Revenue column.

9. Select Run.

10. Select ‣ Visualization.

You should see a visualization and table similar as shown below.

The query generated by Looker using the BI extension is including ORDER BY, which implies that the sort is executed through Looker and the BI extension.

-- Looker Query Context '{"user_id":6,"history_slug":"fc83573987b999306eaf6e1a3f2cde70","instance_slug":"71d4667f0b76c0011463658f45c3f7a3"}'
SELECT
    cc_data_view."product_name"  AS "cc_data_view.product_name",
    COALESCE(SUM(CAST(( cc_data_view."purchase_revenue"  ) AS DOUBLE PRECISION)), 0) AS "purchase_revenue"
FROM
    "public"."cc_data_view" AS "cc_data_view"
WHERE ((( cc_data_view."daterange"  ) >= (DATE_TRUNC('day', DATE '2024-01-31')) AND ( cc_data_view."daterange"  ) < (DATE_TRUNC('day', DATE '2023-02-01'))))
GROUP BY
    1
ORDER BY
    2 DESC
FETCH NEXT 500 ROWS ONLY

1. Enter the following statements in a new cell.

   code language-python
   data = %sql SELECT product_name AS `Product Name`, SUM(purchase_revenue) AS `Purchase Revenue`, SUM(purchases) AS `Purchases` \ FROM cc_data_view \ WHERE daterange BETWEEN '2023-01-01' AND '2023-02-01' \ GROUP BY 1 \ ORDER BY `Purchase Revenue` DESC \ LIMIT 5; display(data)

2. Execute the cell. You should see output similar to the screenshot below.

   

The query is excuted by the BI extension as defined in Jupyter Notebook.

1. Enter the following statements between ```{r} and ``` in a new chunk.

   code language-r
   ## Dimension 1 Sorted df <- dv %>% filter(daterange >= "2023-01-01" & daterange < "2023-02-01") %>% group_by(product_name) %>% summarise(purchase_revenue = sum(purchase_revenue), purchases = sum(purchases), .groups = "keep") %>% arrange(desc(purchase_revenue), .by_group = FALSE) print(df)

2. Run the chunk. You should see output similar to the screenshot below.

   

The query generated by RStudio using the BI extension is including ORDER BY, which implies that the order is applied through RStudio and the BI extension.

SELECT
  "product_name",
  SUM("purchase_revenue") AS "purchase_revenue",
  SUM("purchases") AS "purchases"
FROM (
  SELECT "cc_data_view".*
  FROM "cc_data_view"
  WHERE ("daterange" >= '2023-01-01' AND "daterange" < '2023-02-01')
) AS "q01"
GROUP BY "product_name"
ORDER BY "purchase_revenue" DESC
LIMIT 1000