Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Noor51
Contributor III
Contributor III

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

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

Noor51_0-1730145845419.png

 

Labels (1)
10 Replies
Qrishna
Master
Master

please provide some sample data.

Noor51
Contributor III
Contributor III
Author

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
Master
Master

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
Contributor III
Contributor III
Author

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
Master
Master

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

2489515 - Set Expression Showing when a Member moves Bucket from a time frame (1).PNG

 

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
Contributor III
Contributor III
Author

It just from Q1 to Q2 

 

Thanks for your help.

Noor51
Contributor III
Contributor III
Author

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
Master
Master

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;

2489515 - Set Expression Showing when a Member moves Bucket from a time frame (2).PNG