Sum Amount Before Specific Date Across The Month Dimension

Mon, 02 Feb 2015 23:33:32 GMT

Hi,

Basically I have a fact and a date dimension tables as shown here:

FactTable:

|FactTransDate | Amount | |------------------| |01/04/2012 | 256 | |02/12/2012 | 2056 | |03/07/2012 | 112 | |04/09/2012 | 95 | |05/05/2012 | 1069 | |07/13/2012 | 125 | |08/17/2012 | 73 |

|09/21/2012 | 73 |

|10/16/2012 | 73 |

|11/17/2012 | 73 |

|12/25/2012 | 73 |

|01/04/2013 | 456 | |02/12/2013 | 4056 | |03/07/2013 | 412 | |04/09/2013 | 45 | |05/05/2013 | 4069 | |07/13/2013 | 425 | |08/17/2013 | 43 |

|09/21/2013 | 473 |

|10/16/2013 | 373 |

|11/17/2013 | 273 |

|12/25/2013 | 573 |

|01/04/2014 | 3456 | |02/12/2014 | 44056 | |03/07/2014 | 2412 | |04/09/2014 | 345 | |05/05/2014 | 24069 | |07/13/2014 | 4425 | |08/17/2014 | 243 |

|09/21/2014 | 3473 |

|10/16/2014 | 4373 |

|11/17/2014 | 3273 |

|12/25/2014 | 6573 |

And the Date Dimension table has simply these fields: Year, Month, Date.

What I need to do is when I select a year from the calendar is to sum the ACCUMULATED amount for the beginning and ending for each month (my dimension is the month on a Straight Table Chart) within that year. So my Output should be something like this when I select 2014 for example:

--------------------------------------------------------------------------------------------------------

DateDimMonth | BeginningAmount | EndingAmount

Jan | XXXX (total everything before Jan 2014) | BeginningAmount + All Trans Amount of current month

Feb | Jan 2014 Ending Amount | Beginning + Current Amount

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

--------------------------------------------------------------------------------------------------------

I tried this definition for the BeginningAmount:

sum({1<FactTransDate= {"<$(=min([TransactionDateDimCalendar.Date]))"}>} [Amount] )

And this for EndingAmount:

sum({1<FactTransDate= {"<$(=max([TransactionDateDimCalendar.Date]))"}>} [Amount] )

So both of them work fine if I don't add the month calendar dimension to the straight table chart. But when I add the monthly dimension, it's calculating the amount for the previous period by grouping the amount on a monthly basis (i.e. All the amounts for Jan across the previous years).

So, my question is: How can sum and aggregate my data and lay it off like the table mentioned above?

Thanks!

Re: Sum Amount Before Specific Date Across The Month Dimension

sujeetsingh — Mon, 02 Feb 2015 23:41:30 GMT

First of all you have a complete set of to-do task so do not expect to get the complete solution in this thread.

I will recommend you to first follow the community with Accumulation and all facts related to it.

I think in spite of adding a lot of conditions in calculation you should opt for the script.

Now for beiginnng amount use the logic that Datte >= selected date.

topic Sum Amount Before Specific Date Across The Month Dimension in QlikView

Sum Amount Before Specific Date Across The Month Dimension

Re: Sum Amount Before Specific Date Across The Month Dimension