Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all - I'm struggling to solve a problem. Any help would be much appreciated. I'm using the following tables.
I'm trying to create a pivot table that will have Conf_Name, Conf Date as dimension. The expression should show sum of all sales$ after the respective Conf_Date for each Conf_Name. We wanted to know how much sales came in after the Conf was held. I'm using the below expression and it works fine only when I select a Conf Name from the List box. If none of the Conf names are selected, the pivot table shows no data and its blank, even dimension values are disappearing. I would like to show all the Conf names in the pivot table and sales after their Conf Date like a summary view. I tried different types of set analysis and nothing worked. Sales date and Conf date are in same format. no issues with data format.
Please help.. I'm breaking my head with this ..
sum( {< [Sales_Date] = {">$(=[Conf_Date])"}>} [Sales_$]) |
Rep Table | |
Rep Id | Rep Name |
123 | John |
456 | Tom |
234 | Jack |
853 | Jim |
Conference_table | |||
Conf_Id | Conf_Name | Conf Date | Rep Id |
ABC | LA Conf | 6/15/2018 | 123 |
ABC | LA Conf | 6/15/2018 | 456 |
DEF | Boston Conf | 8/1/2018 | 234 |
DEF | Boston Conf | 8/1/2018 | 853 |
Sales Table | ||
Rep Id | Sales Date | Sales$ |
123 | 5/1/2018 | 100 |
123 | 6/3/2018 | 100 |
123 | 7/1/2018 | 200 |
123 | 8/15/2018 | 300 |
234 | 6/3/2018 | 200 |
234 | 7/1/2018 | 250 |
234 | 8/15/2018 | 100 |
234 | 9/1/2018 | 250 |
Below is the expected result.
Expected Results | |||
Conf_Id | Conf_Name | Conf Date | Sales$ After Conf |
ABC | LA Conf | 6/15/2018 | 500 |
DEF | Boston Conf | 8/1/2018 | 350 |
Thank you.
you may need to do something like
sum({<OrderId={'=[Sales_Date] > [Conf_Date]'}>} [Sales_$])
check this out.
Best to create a flag in the back end.
set hide prefix = '%';
If (Sales Date > Order Date, 1,0) as %Flag_Sales_greater_order
and in front end:
Sum({<Flag_Sales_greater_order ={1}>}Sales)