Qlik Sense Row by Row Comparison from current month vs previous month

[Sahi]

Hello everyone,

Please help me with the below scenario where am facing the issue. I have data for months(January, February, March . etc.,) In the below example I provided data for 2 months (March and April). 

Id	Month-Year	Stage
1	March	Stage 1
2	March	Stage 3
3	March	Stage 2

Id	Month-Year	Stage
1	April	Stage 1
2	April	Stage 2
3	April	Stage 3

I want to compare the Id's row by row for April vs March.(compare April as latest and March as old data)

Scenario 1 : Id 1 related Stage value is same in April and March so the value should be "Match"

Scenario 2 : Id 2 in March  its Stage value is "Stage 3" where as in April its "Stage 2", so its demoted so the value to be "Down"

Scenario 3 : Id 3 in March it Stage value is "Stage 2" where as in April its "Stage 3" so its promoted so the value to be "Up".

Final Result to be as below :

Id	Expected Result
1	Match
2	Down
3	Up

Any help is helpful !.

Thanks

[G3S]

to get last month's value (it's long because I've tried to have Dec last year to compare with Jan this year.)

If(Year(Today())>Year(Monthstart(AddMonths(Today(),-1))),
({$YourMONTHfield<={"12"},YourYEARfield={"$(=num(year(today())-1))"}>}Stage),({$<YourMONTHfield={"$(=num(month(today()))-1)"},YourYEARfield="$(=num(year(today())))"}>}Stage)

then it would be comparing with the current month and if statement based on how the value in 'stage' moved between the two. 

[Gabbar]

Doing it in load Script will be much easier,
i hope all data is in same table, but if it is in different tables as the data structure is same you can just concatenate all the table.

And i also hope that you have a datefield, rather than just month, if you dont have dates, just have a month and year column, you must create a datefield either monthstart date or monthend date(you can search it and if you dont understand that i will tell), i am considering monthstart as datefile
 Source:
Load id,Monthstart,keepchar(Stage,'1234567890') as Stage from Source(Concatenate all data or is a single table upto you);

noconcatenate
A:
Load *, If(id = previous(id),If(Stage=previous(Stage),'MATCH',If(Stage<previous(Stage),'DOWN',If(Stage>previous(Stage),'UP',Null()))),null());
load * resident Source order by id asc, monthstart asc;

Drop Table Source;

[Sahi]

Hi Akash,

Thanks for taking time into this.

I am actually looking for a Flag in script where we cane have 3 values like (Match,UP,DOWN).

I have my Id's and Stage in different tables but I tried as you suggested to concatenate the 2 tables.

The logic you suggested is not working as I don't see the Flag values as expected.

I see may be similar to below works but I am unable to implement (my assumption)

if(Previous Month Stage Id  = 1 and Current Month Stage ID = 1, 'Match',

if(Previous Month Stage Id  = 2 and Current Month Stage ID = 2, 'Match',

if(Previous Month Stage Id  = 3 and Current Month Stage ID = 3, 'Match',

if(Previous Month Stage Id  = 1 and Current Month Stage ID = 2, 'Up',

if(Previous Month Stage Id  = 1 and Current Month Stage ID = 3, 'Up',

if(Previous Month Stage Id  = 2 and Current Month Stage ID = 3, 'Up',

if(Previous Month Stage Id  = 1 and Current Month Stage ID = 2, 'Down',

if(Previous Month Stage Id  = 2 and Current Month Stage ID = 3, 'Down',

if(Previous Month Stage Id  = 3 and Current Month Stage ID = 1, 'Down'))))))))) as Flag

Am thinking this will work so that I can use that "Flag" field where ever I need to show only "Up" related records or "Down" accordingly in my Set Analysis.

Thanks,

Sahi

[Sahi]

Hello G3S,

Its not particularly to get the last month value, its like comparing last month vs the before month.

April vs March :

If in March its Stage 2 and in April its Stage 3 then its Up,

If in March its Stage 1 and April its Stage 2 then its Up,

If in March its Stage 1 and April its Stage 3 then its Up

If in April its Stage 3 and March its Stage 1 then Down

If in April its Stage 2 and March its Stage 1 then Down

If in April its Stage 3 and March its Stage 2 then Down

On the whole its not only for April vs March, it could be April vs Feb or Feb vs Jan and so on.

Hope this helps my issue.

Thanks,

Sahi

[Gabbar · Accepted solution]

the if statement I used is the flag, i tried the data you have shown as Inline Data,

here 45016 is the numerical value of month-end of march 2023 and 45046 for april 2023

This is the table formed, all are respective dimensions and no measures.
If you want to Show stage as Stage-1, Stage -2 etc , then either use concat in UI or create a new field where you keep the real name, and same for your Month-Year Column.


You have just showed your if statement and that seems correct the only error can be that you havent ordered your data, but 9 ifs and every if having an AND condition will make the  load script slower.

[Sahi]

Hi Gabbar,

Small change for the above logic.

I made changes and created the Flag for "Up" and "Down". Both the flag values related logic is correct if we consider the dimension as Stage.

Qlik Logic(the flag what we created)

Dimension	Up	Down
Stage	Current Month Stage Value	Current Month Stage Value

Requirement

Dimension	Up	Down
Stage	Current Month Stage Value	Previous Month Stage Value

Can you please help how can I achieve the Down logic as per the ask.

Thanks

[Gabbar]

Can you explain it a bit more with example?