Removing records which had data for Last month but not this monthT

[#1]

Hi All,

I have a dashboard which calculates YTD Values  for stores. These stores roll up to markets. I have one store "29875" which belonged to "Market A" on the month of January, but has moved to "Market B" from the month of February onwards. Since, my App is of incremental nature, the sales amount for that store for the month of January still carries on when I do the YTD (June) for Market A.

My YTD Formula is as follows:

num(Sum({$<YEAR={$(vMaxYear)}, MONTH_NO={"<=$(=vMaxMonth)"}, ,MONTH=>}US_AMOUNT)/(Sum({$<YEAR={$(vMaxYear)}, MONTH_NO={$(vMaxMonth)},MONTH=>}YTD_Days)),'$#,##0;($#,##0)')    

Here vMaxYear=2012 & vMaxMonth=7                   

This above expression calculates the YTD value for all the stores (for June) under "Market A" along with the store "29875" taking the amount of month of "January". This store moved out to "Market B " after "January".

How can I get rid off the amount associated with the store "29875" for all the months after January for "Market A' so that YTD(Feb) or YTD(June) etc doesn't include the amount associated with this store anymore. The YTD values of store "29875" should only account for "Market B" from February onwards which it does.

Bottomline: I dont want the Market A to carry over the YTD values when the store doesn't belong to him on that month.

All suggestions will be highly appreciated.

Thanks,

Tutan

[nagaiank]

This is a case of slowly changing dimension which can be handled in Qlikview. You need to add market as a dimension instead of having it as an attribute of store:

Month	Store	Market	Sales
Jan 2012	29875	Market A	1000
Feb 2012	29875	Market B	1200

[#3]

Hi krishnamoorthy,

Thanks for your response. I have a hierarchy in which Markets have stores. In other words, stores belong to markets.Both stores and markets belong to my location dimension. It would really help if you can elaborate your suggestion.

Thanks,

Tutan

[#4]

Also to add to it, my lowest level of granularity in stores.

[nagaiank]

The stores to market hierarchy in your case is not constant or fixed as it is changing with time, the market being the slowly changing dimension. The OLAP modeling uses well-known methods (Type-1, Type-2, Type-3, Type-6, etc.) for handling slowly changing dimensions.

In this case if you add market as a dimension, instead of using market derived from the hierarchy, you will get your desired results.

[#6]

Thanks .. I understand, but is there any other option to exclude the store while calculating YTD given the same hierarchy?