MonthToDate not working in table

[#1]

Hi All,

i am creating a table that have Month to Date value in it, using set analysis.

if i use "Department" as dimension in the table, the MTD value will shown correctly.

But if i change to "Date" dimension the value seems to breakdown for each day not accumulated.

I have attached an image showing what result that i am expecting and the result that currently QV shown.

I use 3 selection list box in the report, "Year" (e.g 2012,2013), "Month" (Jan, Feb), and Day (e.g 1,2,3,4) and must be at least one value selected properties are checked.

and my set analysis for MTD is : sum({$<Day={"<= $(=max(Day))"}>} [Sales Amount Gross]).

Thanks in advance.

[#2]

Hi

PFA... Hope it will help you

Regards

Nirmal

[VishalWaghole]

Hi Piyajate,

Try this expression for your MTD

=sum({<Date = {">=$(=Date(Max(Monthstart(Date))))

                         <=$(=Max(Date))"}>} [Sales Amount Gross])

Hope it will work for you.

- Regards,

Vishal Waghole

[#4]

Hi,

i am still not getting the expected result.

What i was expecting is let say the value are like below :

• 1 Jan = 5
• 2 Jan = 10
• 3 Jan = 15

for the MTD i am expecting that for 2 Jan it will be accumulated between 1 and 2 Jan value (5+10) which is 15, like below :

• Date | MTD Value
• 1 Jan | 5
• 2 Jan |15
• 3 Jan | 30

if the dimension were change to other than "Date" then my MTD expression will work correctly, but not if using Date dimension

[#5 · Accepted solution]

hi

PFA.... refer the two straight tables hope it will helps you

[#6]

Hi Nirmal,

it worked!

Thanks alot!

[#7]

Hi Nirmal,

it worked!

Thanks alot!