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

[Noor51]

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'
)

[Qrishna]

please provide some sample data.

[Noor51]

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 %

[Qrishna]

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.

[Noor51]

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 %

[Qrishna]

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?

[Kushal_Chawda]

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

[Noor51]

It just from Q1 to Q2 

Thanks for your help.

[Noor51]

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.

[Qrishna]

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;