Need Help

[narender123]

Hi All,

I have made a calender which is  based on "receipt data" of a table.

There is a paid date also.

receipt date   paid date      amount

1-1-2012        5-1-2012          100

25-2-2012      5-3-2012           200

20-2-2012      26-2-2012          288

--------

I want to calculate amount where receipt date and paid date both are of the same month.

for those where receipt date and paid date are not belong to same month then not show there amount.

if i select september month then only show those amount whose paid date and receipt date fall in the same month.

Please suggest me to sort out this.\

Thanks in Advance.

Narender

[swuehl]

Maybe something like

=sum( if(monthstart([receipt date]) = monthstart([paid date]), amount))

or create a flag in your data model table for that and just filter that flag.

[narender123]

Thanks for the reply Swuehi.\

I will try your 1st expression

for 2nd option can you give me the example as i shown in my question to achieve flag?

Thanks.

[israrkhan]

Try Like below:

Sum({<[receipt date]= {>=$(=MonthStart([receipt date])) ,<= $(=MonthEnd([receipt date])), [paid date]= {>=$(=MonthStart([Paid date])) ,<= $(=MonthEnd([paid date]))}>} Amount)

[swuehl]

Assuming all three fields are within a single table in your data model:

LOAD *,

          if(monthstart([receipt date]) = monthstart([paid date]), 1,0) as SameMonthFlag

INLINE [

receipt date,   paid date,      amount

1-1-2012,        5-1-2012,          100

25-2-2012,      5-3-2012,           200

20-2-2012,      26-2-2012,          288

];

Then in your chart:

sum({<SameMonthFlag = {1}>} amount)

or

sum( SameMonthFlag * amount)

[narender123]

Hi Swuel,

Its not working for me .Its showing "_" means not working..There is change in my example and the change is that receipt date and paid date fields belongs to  different table and joining between them via doc_id.

Thanks.

Narender

[narender123]

Hi Israrkhan,

Its not working ,it is showing error with RED line in expression .

Thanks.

[Anonymous]

" I want to calculate amount where receipt date and paid date both are of the same month."t you want and i

if this is want and receipt date and paid date  and amount all are of same table

create a flag like this in script (in your table)

if (num(month(paiddate)) =  num(month(receiptdate)),1,0 )  as Flag

then use expression like this    sum(if(Flag=1, Amount) or if (Flag=1, sum(Amount))

anant

[swuehl]

Then please upload an updated sample of your data (i.e. the two or more tables involved, incl. some lines of sample data). What's the relationship between the two tables? 1:1 or 1:n?

[narender123]

Thanks Swuehl.

i have used this expression.

=if(monthstart(Date) = monthstart(event_date),sum({<REGIME={'C*'} ,TAX_CODE={'VT'} >}TAX*-1))

But its showing "_".

I have also tried with this:

=sum(if(monthstart(Date) = monthstart(event_date),sum({<REGIME={'C*'} ,TAX_CODE={'VT'} >}TAX*-1)))

Its also showing same "_".

Suggest me to sort out this.

Thanks