Discussion board where members can learn more about Qlik Sense App Development and Usage.
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:
@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
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
Hello @Kush ,
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.
@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.