topic Re: Set Expression Showing when a Member moves Bucket from a time frame in App Development

Set Expression Showing when a Member moves Bucket from a time frame

Noor51 — Wed, 30 Oct 2024 13:15:46 GMT

Hello Everyone,

I'm trying to write a set expression to show when Member moves bucket between Q1-2024 and Q2-2024 in a table.

This is for Qlik sense on perm version May 2023.

Any help would be appreciated.

This is what I have so far

If(
FirstSortedValue(
{<QuarterYear={'Q1-2024'}, [Class of Trade]={'ACUTE'}>} Bucket,
[Member]
) <>
FirstSortedValue(
{<QuarterYear={'Q2-2024'}, [Class of Trade]={'ACUTE '}>} Bucket,
[Member]
) and
Not IsNull(FirstSortedValue(
{<QuarterYear={'Q1-2024'}, [Class of Trade]={'ACUTE '}>} Bucket,
[Member]
)),
'Moved',
'No Change'
)

Re: Set Expression Showing when a Member moves Bucket from a time frame

Qrishna — Tue, 29 Oct 2024 07:23:53 GMT

please provide some sample data.

Re: Set Expression Showing when a Member moves Bucket from a time frame

Noor51 — Tue, 29 Oct 2024 13:07:14 GMT

Does this help as what data is in the fields?

Member	QuarterYear	Bucket
001 IL	Q1-2024	> 64 %
002 AZ	Q2-2024	65 - 74%
003 WI	Q3-2024	75 - 84 %
004 NY	Q1-2024	85 - 90 %

Re: Set Expression Showing when a Member moves Bucket from a time frame

Qrishna — Wed, 30 Oct 2024 05:36:34 GMT

Hi just see only one bucket for each member. need some more rows with different buckets for different quarter and [FK Member] field is missing too, need that field as i see you are comparing that field with Member field.

Re: Set Expression Showing when a Member moves Bucket from a time frame

Noor51 — Wed, 30 Oct 2024 13:27:08 GMT

The [FK Member] field should be just [Member] field. Here is a little bigger data set. Thanks for all your help.

Member	QuarterYear	Bucket
001 IL	Q1-2024	> 64 %
002 AZ	Q2-2024	65 - 74%
003 WI	Q3-2024	75 - 84 %
004 NY	Q1-2024	85 - 90 %
001 IL	Q2-2024	85 - 90 %
002 AZ	Q1-2024	75 - 84 %
003 WI	Q2-2024	65 - 74%
004 NY	Q2-2024	> 64 %
005 OH	Q3-2024	75 - 84 %
006 WY	Q2-2024	65 - 74%
005 OH	Q4-2024	65 - 74%
006 WY	Q3-2024	> 64 %

Re: Set Expression Showing when a Member moves Bucket from a time frame

Qrishna — Mon, 04 Nov 2024 15:07:46 GMT

the order on UI should be same as that from the data you've provided? or like below is fine?

Not sure why you wanna hard code the QuarterYear and not make it dynamic?

Re: Set Expression Showing when a Member moves Bucket from a time frame

Kushal_Chawda — Mon, 04 Nov 2024 13:20:15 GMT

@Noor51 Is the movement required from Q1 to Q2 or between any quarters?

Re: Set Expression Showing when a Member moves Bucket from a time frame

Noor51 — Mon, 04 Nov 2024 14:45:00 GMT

It just from Q1 to Q2

Thanks for your help.

Re: Set Expression Showing when a Member moves Bucket from a time frame

Noor51 — Mon, 04 Nov 2024 14:46:05 GMT

We get a raw data file from a vendor and that how they format it so we just use hard code. Thanks you for looking into this. I appreciate all your help.

Re: Set Expression Showing when a Member moves Bucket from a time frame

Qrishna — Mon, 04 Nov 2024 16:06:54 GMT

Try Below:

Data:
Load RowNo() as Sno, Member& '|'&QuarterYear as %Key,*;
Load * Inline [
Member,QuarterYear,Bucket
001 IL,Q1-2024,> 64 %
002 AZ,Q2-2024,65 - 74%
003 WI,Q3-2024,75 - 84 %
004 NY,Q1-2024,85 - 90 %
001 IL,Q2-2024,85 - 90 %
002 AZ,Q1-2024,75 - 84 %
003 WI,Q2-2024,65 - 74%
004 NY,Q2-2024,> 64 %
005 OH,Q3-2024,75 - 84 %
006 WY,Q2-2024,65 - 74%
005 OH,Q4-2024,65 - 74%
006 WY,Q3-2024,> 64 %
];

left join(Data)
Load %Key,
if(Member = Previous(Member) and QuarterYear = 'Q2-2024' and Previous(QuarterYear) = 'Q1-2024' and Bucket <> Previous(Bucket), 'Change', 'No Change') as Flag
;
Load * Resident Data
order by Member, QuarterYear;

Re: Set Expression Showing when a Member moves Bucket from a time frame

Kushal_Chawda — Mon, 04 Nov 2024 15:37:46 GMT

@Noor51 It is always better to do this kind of calculation in the script.

Data: Load * Inline [ Member QuarterYear Bucket 001 IL Q1-2024 > 64 % 002 AZ Q2-2024 65 - 74% 003 WI Q3-2024 75 - 84 % 004 NY Q1-2024 85 - 90 % 001 IL Q2-2024 85 - 90 % 002 AZ Q1-2024 75 - 84 % 003 WI Q2-2024 65 - 74% 004 NY Q2-2024 > 64 % 005 OH Q3-2024 75 - 84 % 006 WY Q2-2024 65 - 74% 005 OH Q4-2024 65 - 74% 006 WY Q3-2024 > 64 % ] (delimiter is '\t'); final: Load *, if(Member=Previous(Member) and WildMatch(QuarterYear,'Q2*') and WildMatch(previous(QuarterYear),'Q1*') and Bucket<>Previous(Bucket),'Change','No Change') as Flag Resident Data Order by Member,QuarterYear; Drop Table Data;