Exclude future months from bar chart

[#1]

Hi,

Please find my sample solution and required excel files attached.

I want to exclude future months from the dimension based on the month selection.

The business logic is: When the user selects July, only data for July from July file should be displayed because it doesn’t make sense to show data from future files.

Whereas when user selects Aug it should show Aug data from July file as well as from Aug file but not from Sep file.

And lastly when a user selects Sep it should show data for Sep from all the three files.

Attached is a sample screen shot of expected solution.

Appreciate all the help.

Regards,

Sachin

[swuehl]

If it doesn't make sense to show data from future files, I would exclude the data already in the data model:

Data:

CrossTable(MONTH, Data, 3)

LOAD Metric,

    Date#(FileBaseName(),'YYYYMMM')  as FileName,

     Country,

     [42005],

     [42036],

     [42064],

     [42095],

     [42125],

     [42156],

     [42186],

     [42217],

     [42248],

     [42278],

     [42309],

     [42339]

FROM

[2015*.xlsx]

(ooxml, embedded labels, table is Data);

New:

noconcatenate

LOAD *,

Year(trim(MONTH)) as Year,

month(trim(MONTH)) as Month,

monthname(trim(MONTH)) as MonthYear

Resident Data

WHERE FileName <= MonthName(Trim(MONTH));

DROP Field MONTH;

DROP Table Data;

[#3]

Hi Stefan,

We should not do it at the data model level. Because the months have to be calculated based on the month year selection.

[swuehl]

You can try using a field event trigger and select in fields actions. Have a look at attached sample.

[#5]

Hi Stefan,

Thank you!! Just one last request, can we put this condition in set analysis instead of trigger.

[#6]

Hi Stefan,

Can we put that condition in set analysis instead of trigger.

[swuehl]

Sure.

=sum({<FileName = {"=FileName<=MonthYear"}>}Data)

[sunny_talwar]

May be with if statement:

=Sum(If(FileName <= MonthYear, Data))

Used Stefan's qvw file to try it out

[#9]

Both yours and Stefan's expressions are working your attached solution. But when I'm implementing in my excel. It doesn't seem to work.

[swuehl · Accepted solution]

Have you taken care that FileName is interpreted as Date as shown in the sample script and above?

LOAD Metric,

    Date#(FileBaseName(),'YYYYMMM')  as FileName,

     ....

You may need to adapt the format code if your files are named differently.