YTD Set Analysis for bar chart showing Working day of Month as dimension.

[retko1985]

Hello,

I need some help to construct set analysis.

Here as a problem I am solving. (See attached QVW below). My graph has a dimension Working day of Month and as the expression i Sum(Sales). For each working day of month, there is an actual date (see inline load data in script).

What I need to do is this: When I click on Month Feb, the data should keep accumulating from where it ended in January, but to keep the dimension of working days of Month.

So, 31.1.2017 Sales accumulation is 4960.

When I click FEB

on 1.2.2017 Sales accumulation should be 4960 + 320 = 5280

on 2.2.2017 it should be 5280 + 330 = 5610.

I need to do it in Set Analysis I guess.

Thank you for any help.

[mostwanted123]

Hi Retko,

First, your date is in text format.

To change in Date format write,

Date(Date#(actualdate,'DD-MM-YYYY'),'DD/MM/YYYY') as ActualDate.

Now, in the YTD expression in Bar Chart write,

Sum({<ActualDate={">=$(=Yearstart(max(ActualDate)))<=$(=max(ActualDate))"},Month=,Year=>}Sales)

Regards,

Pratik

[retko1985]

Can you please transform my QVW an upload it here?

Because I still don't see the correct accumulation when I try it.

Thank you.

[mostwanted123]

Please check this.

[retko1985]

Thank you, but this is not correct.. You probably also forgot to ignore month and year in set analysis. I added that and here is what I get (see attached file):

On 1 day of January it shows 350, which is 320 + 10 + 30. So it recognizes only working day 1 from January, but I want to add all working days from January.

As I wrote before, for 1 day in February it should be this 4960 + 320 = 5280

Thank you.

[retko1985]

Noone wants to play with this?

[sunny_talwar]

Try this

RangeSum(Above(Sum(Sales), 0, RowNo())) + Sum(TOTAL {<actualdate = {"<$(=Min(actualdate))"}, Month, Year>}Sales)

and make sure to use no accumulation