Adding values once conditions have been met from 2 different fields

[#1]

I have the following data and need some help.

DATE_DATA (dates)

DAYS (numbers from 1 to 31. Some months the max is 30)

ACCOUNT (#)

BALANCE ($)

I need to be able to create an expression either in the script or in the front end that will let me add all the Balances for the accounts for the DATE_DATA where the DAYS in the max. If you see the table below, I would need to be able to add the Balances for the accounts for the highlighted days.

In Jan 2014 - it would be for day 01/17/14 since the DAYS is the max for that month (31)

In Feb 2014 - it would be for 02/19/14 since the DAYS is the max for that month (31)

In Mar 2014 - it would be for 03/19/14 since the DAYS is the max for that month (28)

I have attached data to use as an example. It is important to note that within each DATE_DATA, there are many different DAYS. First I need to calculate the Max Day for the month to be able to identify which date it is that I need to use to add all the balances for my accounts.

DATE_DATA	Max (DAYS)
	31
20140102	14
20140103	17
20140106	18
20140107	19
20140108	20
20140109	21
20140110	24
20140113	25
20140114	26
20140115	27
20140116	28
20140117	31
20140121	2
20140122	3
20140123	4
20140124	7
20140127	8
20140128	9
20140129	10
20140130	11
20140131	14
20140203	15
20140204	16
20140205	17
20140206	18
20140207	21
20140210	22
20140211	23
20140212	24
20140213	25
20140214	29
20140218	30
20140219	31
20140220	1
20140221	4
20140224	5
20140225	6
20140226	7
20140227	8
20140228	11
20140303	12
20140304	13
20140305	14
20140306	15
20140307	18
20140310	19
20140311	20
20140312	21
20140313	22
20140314	25
20140317	26
20140318	27
20140319	28
20140320	1
20140321	4
20140324	5
20140325	6
20140326	7
20140327	8
20140328	11
20140331	12

[MK_QSL]

use below script

LOAD Date#(DATE_DATA,'YYYYMMDD') as DATE_DATA,

  Date(MonthStart(Date#(DATE_DATA,'YYYYMMDD')),'YYYYMM') as MonthYear,

     ACCOUNT,

     DAYS,

     BALANCE

FROM

Example.xlsx

(ooxml, embedded labels, table is Sheet1);

Now

create a straight table

Dimension

MonthYear

Expression

SUM(Aggr(IF(DAYS = Max(TOTAL <MonthYear> DAYS),SUM(BALANCE)),MonthYear,DAYS))

[#3]

Good afternoon Manish,

Thank you for the response. I was able to get the formula to work, but it isn't giving me the correct sum.

Using the expression you provided me with, I am getting the following discrepancies:

Jan - Date 01/17/2014

     -Correct Sum: $24,140

     -Sum from expression: $23,516

Feb - Date 02/19/2014

     -Correct Sum: $26,624

     -Sum from expression: $25,490

Mar- Date 03/19/14

     -Correct Sum: $25,529

     -Sum from expression: $24,821

I also then need to be able to have that data filter to my account numbers. Meaning that once I select MonthYear "Jan 2014" I need to be able to have the correct account numbers and balances filtered out for date 01/17/14 because I need to go into the detail of account and segment that data into smaller pieces by later linking my segmentation excel table. Currenly if I select MonthYear, Its licking to the entire month and not providing me with the account numbers that are relevant to just to the day 01/17/14.

[#4]

Good afternoon Manish,

I figured out the error of why the differences in the sum. It is only counting the records that have the max number of Days identified under its DAYS. For instance, we were able to identify that for Jan 2014, the DATE_DATA with the max DAYS was 01/17/14. Now with the expression you provided me, it is only adding the accounts on that day that have a 31 as the DAYS. The problem is that I need to have the sum of all the accounts under that DATE_DATA for all the DAYS, not just the ones that have 31. Do you think this can be fixed?

Thank you for the help!