Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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)
Highlighted
Contributor III
Contributor III

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

Highlighted
Contributor III
Contributor III

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