Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have the following table containing transactions for two stores :
Date | Store Name | # Orders | Visitors Count |
01-01-2019 | S1 | 5 | 0 |
01-01-2019 | S2 | 5 | 0 |
02-01-2019 | S1 | 5 | 0 |
02-01-2019 | S2 | 5 | 10 |
03-01-2019 | S1 | 5 | 10 |
03-01-2019 | S2 | 5 | 10 |
The order and visitor details are not available from same dates. The order details are present from 01-01-2019 whereas the visitor details are present from different dates for each store. I want to compare number of orders against number of visitors for each store. To get correct numbers, I need to find out number of orders starting with dates when both order and visitor details are present (and not from 01-01-2019).
I have used expression Date(Min(Aggr(If(Sum([Visitors Count]) <> 0, Date), Date,[Store Name]))) to get the minimum date from which visitor details are present for each store. Now I want to use this date in set analysis and find out number of orders for dates greater than equal to this date.
For example, store S2 has visitor details from 02-01-2019, so for store S2 total number of orders should be 10 (considering 02-01-2019, since when both order and visitor details are present), instead of 15 (if date 01-01-2019 was considered).
Could anyone please help me here ?
Thanks and Regards,
Madhuparna Dhar
Are you trying to get this in a chart? If so you could try like:
Straight table:
Dim : [Store Name]
Exp : FirstSortedValue ([Visitors Count], - Date)
Hi,
I am trying to get this in a bar chart which will have the store name as dimension and count of orders (starting from date where both order and visitor details are present) and count of visitors.
Thanks and Regards,
Madhuparna Dhar
Hi,
FirstSortedValue ([Visitors Count], -Date) will get me the visitors count only for the last date. That is not what I actually want.
I want the total number of orders and visitors for each store, starting from the date when both order and visitor details are present (i.e >= 0).
For store S1, this date should be 03-01-2019 and for store S2, this date should be 02-01-2019.
Below is the desired output :
Store Name | Total #Orders | Total #Visitors |
S1 | 5 (starting from 03-01-2019) | 10 |
S2 | 10 (starting from 02-01-2019, so 5+5 = 10) | 20 |
Thanks and Regards,
Madhuparna Dhar