Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
madhuparnadhar
Contributor III
Contributor III

Set analysis and Aggr

Hi All,

I have the following table containing transactions for two stores :

DateStore Name# OrdersVisitors Count
01-01-2019S150
01-01-2019S250
02-01-2019S150
02-01-2019S2510
03-01-2019S1510
03-01-2019S2510


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

3 Replies
tresesco
MVP
MVP

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)
madhuparnadhar
Contributor III
Contributor III
Author

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

madhuparnadhar
Contributor III
Contributor III
Author

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 NameTotal #OrdersTotal #Visitors
S15 (starting from 03-01-2019)10
S210 (starting from 02-01-2019, so 5+5 = 10)20

 

Thanks and Regards,

Madhuparna Dhar