Creating filters based on last yr & current yr mom comparison

[salonibhatia]

Hi...I need to create a filter for my dashboard wherein I need to compare current year month  StoreCodes to last year month ones

and then create below 3 conditions

Same- if the store code exists in last year same month as current year ---say jan 2017 and jan 2018 have the same A store code

old--- if the store code does exists in last year in a  month say Jan but is not there in current year Jan month

new- if store code exists in current year in a month and was not there in last year same month

dummy data could be as below

Year	Month	StoreCode
2016	Jan	A
2016	Feb	B
2016	Apr	A
2016	Jun	A
2017	Jan	A
2017	Feb	A
2017	Apr	B
2017	Jun	A
2017	Apr	C
2017	Jun	C
2018	Jan	D
2018	Feb	A
2018	Apr	B
2018	Jun	C
2018	Jan	F

For year 2017, in the month of Jan Store code A exists in 2016 Jan & 2017 Jan thus it should be under same filter

I had tried using applymap based on a key created using year, month & Store code  in the mapping table and then created a column called last year using the year function[to get the same month last yr value] and created the same key in transaction table and got a flag but that gave me the data for same...what about new & old option??

Note-- I need to do this in backend only

[ogautier62]

Hi,

in load try this :

store:

LOAD  * FROM   'your data source'      ;   

load StoreCode, Month, Year, if(peek('StoreCode')=StoreCode and peek('Month')=Month and peek('Year')=Year-1,'Same','New') as flag   resident store order by StoreCode,Month,Year;

so you have now :

for 'Old' status, when you have a graph upon your Calendar,

replace null value for flag by 'old'

regards

[salonibhatia · Accepted solution]

@ogautier62‌ Nice way around....Thanks   but the problem is that I have to do everything in backend as I had to give a filter only..

below is what I have done:

divided data in 2 tables one which had data of 24-12 months[last year] & one that had data of 12-0 months [ current year]..

created a key in both tables using month & store code in both of the tables

Then created mapping table from last year having key & 'same' as storestatus

Done an apply map on the current year table --- giving the unmap codes as new

for the Old one...then concatenated the above current year table using last year table with condition where not exists(key) and stated old as store status...

This solved the issue

[David-Wong]

Hi Saloni,

Saw this post the other day, and just couldnt help myself not to work on it, as I really think this is a very good exercise about data modeling, even though I saw that you have already found your solution.

As per requirement, you are trying to create 3 fields named Same, Old, New based on the following rules:

1.  Same - If store-month appears in this year and last year

2.  Old - If store-month appears in last year only

3.  New - If store-month appears in this year only

The above solution or the 3 fields represent the status of your Store-Month based on CurrentYear=2018 and LastYear=2017.  I was just wondering if user will want to know what will be the Store-Month status if CurrentYear=2017 and LastYear=2016.

E.g.  In Your Example, in Year 2016, you have 4 new Store-Month, and all of them will appear in New field, if user want to know what are the new Store-Month in Year 2016.  In Year 2017, all of the Store-Month status will be changed, for example, A-Jan and A-Jun will have status=SAME, but the other two will have status=OLD.

If the Point in Time of your fact is not a concern and you only need to have the 3 field represent the Current Year/Last Year status, then just treat this as an alternative solution.

The solution that I suggest here is using the following technique:

1.  Determine the StartYear and EndYear of a Store-Month

2.  Create the RefereneYears/PointInTimeYear for all Store-Month using IntervalMatch

3.  Derive the status of each Store-Month based on the ReferenceYear/PointInTimeYear

4.  Instead of creating 3 Status field with Store Code as value, I'm created 1 Status Flag field for the purpose

Based on the above example, if user want to know in Year 2017, what are the store-month have the status Old, this will be the solution screenshot:

Attached is the qvw file for the solution.

David