Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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:
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
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)
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)