I want to sum the quantity for each PO and ASIN, but only distinct ASINs.
I’ve tried sum(min({quantity}, [{po}, asin])) but is not working.
How can I do it?
this seem doing the trick in a pivot with both po and asin as columns
sumOver
(
sum(quantity)/count(asin),
[po,asin]
)
Hello Crizar,
within your Input data, is the second row repeated or can there be different quantity value as well ?
if the quantity is different for row 2 lets say 200 what is the expected output ?
thanks for letting us know.
Cheers,
Deep
The output is not the expected as I get the sum of quantity for each asin within each po.
I would expect to see the sum(quantity) of distinct asins for each po.
Hi Deep,
The second row is repeated, the value is the same for row 1 and 2, but we only want to consider distinct asin quantity.
To sum the quantity for each unique combination of PO and distinct ASIN, you can use the following approach. Assuming you are using Tableau, you can create a calculated field using the WINDOW_SUM function. Here’s an example formula:
{ FIXED [PO], [ASIN] : SUM([quantity]) }
This formula uses the FIXED LOD (Level of Detail) expression to sum the quantity for each unique combination of PO and ASIN. The WINDOW_SUM function then calculates the sum within that fixed level of detail.
After creating this calculated field, you can use it in your visualization to display the summed quantity for each PO and distinct ASIN.
Thanks,
UPSers Login Portal
Hello Crizar,
If its duplicate , its good to restrict the duplicate in the data source or before bringing it to QS. this will make your future calculation more easier and better plus it wont bring duplicate data to QS.
any thought on this ?
Cheers,
Deep
It’s a duplicate in this sample. In the raw data there are other distinct fields.
Hey Crizar,
If there are no duplicates in raw data they i think it will be straight forward.
but could you try this if it helps.
sumOver(quanitity,[asin,PO],PRE_AGG)
Let us know.
Cheers,
Deep
Sorry maybe I was not clear before, I do need the duplicates because the table has other data. I’m adding below an extended example that can help to understand better the use case.
po_sum_quantity is summing the quantity of distinct asins over the po
hello crizar ,I think you can do this with a bit of a hack
1- check the count per asin with runningCount(asin,[date ASC],[asin]) in order to have a row count per asin
2- then you create a measure flag and return the quantity only for first row found with an ifelse → ifelse(runningcount=1,min(quantity),0)
3- sumOver(measure created above,[po])
let me know if it works
2 Likes
Hello Crizar,
The solution suggested by Franc would work absolutely fine. I tried similar approach to reproduce the only difference i did was concatenating po and asin to be more concise and it could result in expected output.
runningCount({concat_po_asin},[datetime ASC],[{concat_po_asin}])
Thanks again to Franc
Cheers,
Deep
1 Like
Yes! This is working. Thank you so much!
Thanks Deep for dedicating time to this. Indeed, this solution is working. Appreciate the support.