topic Re: Measure to count distinct value, comparing dates but ignoring relations in App Development

Measure to count distinct value, comparing dates but ignoring relations

danette — Sat, 16 Nov 2024 01:02:26 GMT

Hello,

I'm trying to develop a chart but I am not able to build a measure in the way I desire.

Consider these two tables:

they are connected through ID_USER.

I want to count the distinct id_shipment_detail where DATE=DATE_TICKET.

With this measure

count(distinct if ( DATE=DATE_TICKET ,ID_SHIPMENT_DETAIL))

it seems to me that the count considers the condition DATE=DATE_TICKET but excludes from the count the row with 'user4' (i believe it is because user4 is not in tableB).

How can I count the total of distinct ID_SHIPMENT_DETAIL, considering the comparison between dates and also including the rows of table A that have no connection with table B?

At the end, if I print a table with DATE, DATE_TICKET and the count measure, I want to obtain the following:

Thank you

Re: Measure to count distinct value, comparing dates but ignoring relations

Kushal_Chawda — Sat, 28 Nov 2020 00:53:19 GMT

@danette you can create a Flag in script like below. Add below piece of code in script after loading table A & table B

left join (TableA) LOAD fieldvalue('DATE_TICKET',recno()) as DATE, 1 as Flag autogenerate fieldvaluecount('DATE_TICKET');

Where DATE_TICKET is field name from table B and DATE is field name of Table A. So that join with table A will be performed on DATE field from both table.

Now, you can use below expression to count the value

count(distinct {<Flag={1}>}ID_SHIPMENT_DETAIL)

Re: Measure to count distinct value, comparing dates but ignoring relations

danette — Sat, 28 Nov 2020 10:09:05 GMT

Hello @Kushal_Chawda ,

thank you for your reply. I have tried your solution but the result is not as I expected or maybe I misunderstood.

Here the result

Consider that I want to create a chart like 'chart n.1' in the picture: I want ticket_date on x-axis and ticket count and shipment count on y-axis.

If I put ticket_date on the x-axis, then the count of ID_SHIPMENT_DETAIL is not correct because returns 2, when it should return 3, because I have 3 shipments in 09/2020.

If I put DATE on the x-axis I have the correct results, but I don't think it's right, because DATE refers to shipments and not to tickets.

Thank you

Re: Measure to count distinct value, comparing dates but ignoring relations

Kushal_Chawda — Sat, 28 Nov 2020 15:54:03 GMT

@danette putting DATE as x-axis is not incorrect because you are joining DATE_TICKET to that table and Flagging according to the matched DATE_TICKET. So basically, you are counting the shipment for DATE_TICKET only.