topic Re: Exclude future months from bar chart in QlikView

Exclude future months from bar chart

Mon, 16 Nov 2015 19:05:09 GMT

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

Re: Exclude future months from bar chart

swuehl — Mon, 16 Nov 2015 19:23:31 GMT

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;

Re: Exclude future months from bar chart

Mon, 16 Nov 2015 19:35:19 GMT

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.

Re: Exclude future months from bar chart

swuehl — Mon, 16 Nov 2015 19:48:53 GMT

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

Re: Exclude future months from bar chart

Mon, 16 Nov 2015 20:13:32 GMT

Hi Stefan,

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

Re: Exclude future months from bar chart

Mon, 16 Nov 2015 20:16:06 GMT

Hi Stefan,

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

Re: Exclude future months from bar chart

swuehl — Mon, 16 Nov 2015 20:21:56 GMT

Sure.

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

Re: Exclude future months from bar chart

sunny_talwar — Mon, 16 Nov 2015 20:23:35 GMT

May be with if statement:

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

Used Stefan's qvw file to try it out

Re: Exclude future months from bar chart

Mon, 16 Nov 2015 21:34:48 GMT

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.

Re: Exclude future months from bar chart

swuehl — Mon, 16 Nov 2015 21:38:12 GMT

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.

Re: Exclude future months from bar chart

Mon, 16 Nov 2015 22:00:05 GMT

Thanks Stefan.. The issue was with FileName.