Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

5 Replies
nagaiank
Valued Contributor III

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

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:

MonthStoreMarketSales
Jan 201229875Market A1000
Feb 201229875Market B1200




Not applicable

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

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

Not applicable

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

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

nagaiank
Valued Contributor III

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

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.

Not applicable

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

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

Community Browser