Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Gavin_FBu
Contributor II
Contributor II

Alternative State to select 2 different time ranges. Some data dropped off after link to different table

Alternative State to select 2 different time ranges so I can show the Sales for Range A and Range B then calculate  the sales variance in the sames table.   It is working fine until the raw data link to another table, some products started to dropped off.

Two Tables in the apps. 
1) Sales Table (contained transaction sales data)

Date Store Product Sales
1/08/2022 ABC Apple 123
2/08/2022 ABC Apple 54
3/08/2022 ABC Orange 78
4/08/2022 ABC Banana 124

 

2) Campaign Table (contain campaign ID and products involved in the campaign)

Campaign ID Product
1 Apple

 

My First step is only use Sales Table to create the following dashboard:

Alternative State for both Date Range Filter (I called it RangeA and RangeB)
A Sales = sum({RangeA<[Store]=$::[Store], [Product]=$::[Product]>}Sales)
B Sales = sum({RangeBA<[Store]=$::[Store], [Product]=$::[Product]>}Sales)

Gavin_FBu_0-1660189838648.png

This is working fine for [Store] as a filter and I can calculate the variance without issue. 

 

My second Step would like to included campaign ID (from second table) as a filter so user can select the Campaign ID and the table will filter to the relative products.  And it looks like this:

Gavin_FBu_1-1660190063519.png

A Sales = sum({RangeA<[Store]=$::[Store], [Product]=$::[Product], [Campaign ID]=$::[Campaign ID]>}Sales)
B Sales = sum({RangeB<[Store]=$::[Store], [Product]=$::[Product], [Campaign ID]=$::[Campaign ID]>}Sales)

Once I done the formula included [Campaign ID], table dropped off all the products that do not existed in Campaign table.  How do I included every product from the sales table and use campaign ID just purely for filtering product?

Thanks
Gavin

Labels (3)
1 Solution

Accepted Solutions
jwjackso
Specialist III
Specialist III

Create an alternate state for the [Campaign ID] field.  Use an intersection of  P()  functions in the formula below:

A Sales = Sum({RangeA<[Store]=$::[Store],[Product]=P({$}[Product])*P({Campaign}[Product])>}Sales)

B Sales = Sum({RangeB<[Store]=$::[Store],[Product]=P({$}[Product])*P({Campaign}[Product])>}Sales)

 

View solution in original post

1 Reply
jwjackso
Specialist III
Specialist III

Create an alternate state for the [Campaign ID] field.  Use an intersection of  P()  functions in the formula below:

A Sales = Sum({RangeA<[Store]=$::[Store],[Product]=P({$}[Product])*P({Campaign}[Product])>}Sales)

B Sales = Sum({RangeB<[Store]=$::[Store],[Product]=P({$}[Product])*P({Campaign}[Product])>}Sales)