Populate new calculated field in all records in a group based on population of another field in the group

[laurafinn]

Hi all,

I feel I may be missing something obvious but just can't get there.

I've attached dummy data to demonstrate what I'm trying to achieve. 

I'd like to create a new field that populates with either Yes or No if a customer has ever had a grant declined.  Each customer will appear multiple times in the data set.  The end goal is to be able to identify those customers that had a declined Grant and to sum the value of the grants they had approved (there's a limit to the value of grants a customer can have within a 12 month period and I want to be able to see if the value of the grants they's already had is the reason they were declined other subsequent grant request).

Laura

[GaryGiles]

If you want to add a Declined_Flag to the table, you could use the following load script:

Grants:
LOAD
"Customer ID",
"Date",
"Grant Status"
FROM [lib://QlikSenseApps/Dummy data Grant app.xlsx]
(ooxml, embedded labels, table is Sheet1);

TempTable:
Load Distinct [Customer ID]
Resident Grants;

Join (TempTable)
Load Distinct [Customer ID],
'Yes' as Declined_Flag
Resident Grants
where [Grant Status] = 'Declined';

Join (Grants)
Load [Customer ID],
if(isnull(Declined_Flag),'No',Declined_Flag) as Declined_Flag
Resident TempTable;

Drop table TempTable;

But, you could also handle this via set analysis without creating a new field.  The advantage being that you could then adjust your set analysis to answer questions like "declined in the last 6 months or having multiple declines".

As an example, to get the list of Customers who have been declined, you could use the following as a dimension:

=aggr(Only({$<[Customer ID]={"=count({$<[Grant Status]={'Declined'}>} [Customer ID])>0"}>} [Customer ID]),[Customer ID])

To recreate the Declined Flag using set analysis, you could use:

=if(Aggr(count({$<[Grant Status]={'Declined'}>} [Customer ID]), [Customer ID])>0,'Yes','No')

Set analysis would be your better option long term.

Hope that helps.