Sum based on different dimension date - Set Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Sum based on different dimension date - Set Analysis

dplr-rn — Mon, 24 Sep 2018 18:52:11 GMT

you may need to do something like

sum({<OrderId={'=[Sales_Date] > [Conf_Date]'}>} [Sales_$])

check this out.

set_analysis_intra-record.qvw

Re: Sum based on different dimension date - Set Analysis

vkish16161 — Mon, 24 Sep 2018 19:59:46 GMT

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)

topic Re: Sum based on different dimension date - Set Analysis in QlikView

Sum based on different dimension date - Set Analysis

Re: Sum based on different dimension date - Set Analysis

Re: Sum based on different dimension date - Set Analysis