Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 |
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
Also to add to it, my lowest level of granularity in stores.
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.
Thanks .. I understand, but is there any other option to exclude the store while calculating YTD given the same hierarchy?