topic Re: Add/Delete Month Over Month Comparision in QlikView

Add/Delete Month Over Month Comparision

Sahi — Tue, 02 May 2023 20:33:18 GMT

Hello Team,

Could you please help me with one of the logic am trying to create.

I have a table with Id's for each month.

Id	Month-Year
1	Jan
2	Jan
3	Jan

Id	Month-Year
1.1	Feb
2	Feb
3.1	Feb

Id	Month-Year
1.1	March
2.2	March
3.1	March

Of the 3 months data above, I am seeing if I can get some logic help created below for Addition/Deletion of Id's to get done in Script may be like a flag (Add/Delete) so that I can use it accordingly in my front end.

Expected output as below:

- Comparing March vs Feb :
- Comparing March vs Jan:
- Comparing Feb vs Jan:

Any help would be more appreciated.

Re: Add/Delete Month Over Month Comparision

Marijn — Thu, 04 May 2023 13:50:59 GMT

@Sahi I'm not sure what exactly you are trying to do, but the script below is a way to add flags whether an ID is present in a specific month. These flags can then be used in a set expression.

Jan: load * inline [ Id,Month-Year 1,Jan 2,Jan 3,Jan ]; Feb: noconcatenate load * inline [ Id,Month-Year 1.1,Feb 2,Feb 3.1,Feb ]; Mar: noconcatenate load * inline [ Id,Month-Year 1.1,March 2.2,March 3.1,March ]; map_Jan: mapping load Id,1 resident Jan; map_Feb: mapping load Id,1 resident Feb; map_Mar: mapping load Id,1 resident Mar; load_all_IDs: load Id resident Jan; concatenate load Id resident Feb; concatenate load Id resident Mar; unique_IDs_with_flags: load distinct Id, ApplyMap('map_Jan',Id,0) as FL_in_Jan, ApplyMap('map_Feb',Id,0) as FL_in_Feb, ApplyMap('map_Mar',Id,0) as FL_in_Mar resident load_all_IDs; drop tables Jan,Feb,Mar,load_all_IDs;

Re: Add/Delete Month Over Month Comparision

Sahi — Thu, 04 May 2023 16:36:54 GMT

Hi Marijn

Thanks for taking time and looking into this.

What I am trying is to compare month over month and find how many Id's are added and got deleted for each month.

. April vs March :

How many new records are Added in April (means they are not in March but in April so Added)

How many of records in March which are not in April so they are considered as Deleted (means they are in March but not in April so Deleted)

As per your logic I see that we need to create the tables for each month, I hope that will not help as we get data each month as we move into future.

Thanks,

Sahi

Re: Add/Delete Month Over Month Comparision

Marijn — Fri, 05 May 2023 06:48:27 GMT

So all the data comes in one table? Like this:

Id	Month-Year
1	Jan
2	Jan
3	Jan
1.1	Feb
2	Feb
3.1	Feb
1.1	March
2.2	March
3.1	March

I think it would be easier to add flags to your months to create flexibility in your analysis and then use set analysis to make the selections. You can add flags for example for current and last month. It would be easiest to create a calendar dimension table for this.
Flags we use a lot are:
FL_CurrentYear
FL_LastYear
FL_CurrentMonth
FL_LastMonth
FL_BeforeCurrentMonth
FL_BeforeCurrentYear
etc.

Then you can select all ID's that are present in this month but not in last month, for example:

only( {$<FL_CurrentMonth = {1}> - <FL_LastMonth={1}>} Id)

Or ID's that are present in current month, but not in any other month:
only( {$<FL_CurrentMonth={1}> - <FL_BeforeCurrentMonth-={1}>} Id)

See this thread on help how to make a calendar table.

Addition(March)	Deletion(Feb)
2.2	2 since not in March

Addition(March)	Deletion(Jan)
1.1	1 since not in March
2.2	2 since not in March
3.1	3 since not in March

Addition(Feb)	Deletion(Jan)
1.1	1 since not in Feb
3.1	3 since not in Feb