Last Price Paid by Different Dimensions

Question & Answer
, , ,
1

I would like to get the most recent price paid by different dimensions in a table.

For example if I have a pivot table visual, with customers in the rows, I would want the most recent price paid regardless of product.

If I added product, so that it was a pivot table by customer and product, it would be the most recent price paid by customer and product.

How can I accomplish this assuming I don’t want to change anything in the data prep phase?

I looked at lastValue but it would require pre-defining dimensions .

Thanks!

2

Hello @MasonWilliams !

Have you tried using the lag function?

3

@duncan good suggestion but this doesn’t work because I can’t use it without dates in the table.

Putting dates into the table would make it unusable for my purpose.

4

Hey @MasonWilliams!

Would adding the date field and hiding the column in the table still not work with your use case? That would allow you to use the function but not show the date field to your user.

5

@duncan It might work - I gave it a try but the calculations aren’t making much sense. Here is my formula and the output.

I would need the sku level (subtotal) to be .55 in this situation

image

image

6

Hey @MasonWilliams !

Since you are aggregating this in the lag function, could you remove the subtotal from the visual to get the desired outcome?

When you collapse the column on the ID you should see the last price paid.

7

Yes but - It still shows a nonsensical number. For example:

000W280TAPE has avg_prices $32.21 and $26.95. Why would last_price_paid_lag be 1.38 and 0.09?

8

Hey @MasonWilliams !

Do you have any other calculations that affect {avg_price} or are you doing any aggregation on the field in the field well?

9

@duncan I checked my calculation and it is sum(revenue)/sum(quantity) which are both from the dataset itself and not calculated.

my other field “date control handler” can be either order date or shipment date depending on a parameter - I changed that to be just order date and I’m seeing the same thing

10

Hey @MasonWilliams !

I’m sorry about the late reply, are you still working on this issue or did you find a solution?

Thank you for the clarification. The lag function should not be transforming the value of the prices, only calling up the most recent price paid.