Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
salonibhatia
New Contributor III

Creating filters based on last yr & current yr mom comparison

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

     

YearMonthStoreCode
2016JanA
2016FebB
2016AprA
2016JunA
2017JanA
2017FebA
2017AprB
2017JunA
2017AprC
2017JunC
2018JanD
2018FebA
2018AprB
2018JunC
2018JanF

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

1 Solution

Accepted Solutions
salonibhatia
New Contributor III

Re: Creating filters based on last yr & current yr mom comparison

@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

3 Replies
ogautier62
Valued Contributor II

Re: Creating filters based on last yr & current yr mom comparison

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

Re: Creating filters based on last yr & current yr mom comparison

@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

Employee
Employee

Re: Creating filters based on last yr & current yr mom comparison

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.

StoreCodeStatus.jpg

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:

StoreCodeStatusQV.jpg

Attached is the qvw file for the solution.

David