How to hide dimension

[#1]

Hi guys,

How to hide the first 5 months, from Jan-2014 to May-2014?

When I update May-2015, Jun-2015 and so on, how to show the latest 12 months and hide the first few months?

[#2]

sum({<MonthYearField-={'Jan-14','Feb-14'}>}Sales)

[Peter_Cammaert]

Or if you want a rolling 12-month chart, going back from last month, use something like:

=sum({<MonthYearDateField =

  {'>=$(=MonthStart(AddMonths(today(),-12)))<$(=MonthStart(today()))'}>} Sales)

Note that in order for this expression to work, MonthYearDateField should have an underlying date value, not just a text value.

[#4]

Thank you Harshal Patil.
how to show the latest 12 month automatically, so I don't need to update definition each month?

[#5]

Hi

Way to achieve your requirement for 12 months financial year if it starts from April-15 create Financial calendar based on that for 2015 startMonth will be April-15 ...

Ex.

//Start with April and consider that fin year as max from both year as year eg 2014-15 , as 2015

if(num(Month(CalendarDate)) > 4 ,(Year(CalendarDate) + 1),Year(CalendarDate) ) AS FIN_YEAR

-or-

YearName(mydate, 0, $(vFiscalYearStartMonth)) AS FiscalYear;

vFiscalYearStartMonth=4

then

sum({<FiscalYr ="$(=Year(today()))">}Sales)

It will show only current fiscal year data & respective months from where it starts ...

[#6]

Thank you Peter. I tried but it doesn't work for corsstable. Could you kindly show me how to wirte this script in corret way? Please find attached files on top of the page.

Thanks again for your help.

[#7]

Thanks for your help.

I am a beginner of Qlikview. Could you kindly show me how to wirte this script in corret way? Please find attached files on top of the page.

[Peter_Cammaert]

Your problem has nothing to do with my expression. In your data model, there is a discrepancy between Month values that originate as column headers, and those that come straight out of an Excel Month column. They are being treated as different values and that is why you have to use this big expression to get a dimension value out of the Month field.

Open the Table Viewer and preview the Synthetic key table. There are two areas of values, (scroll down to see what I mean and rmember: left-aligned means text) numerical ones at the bottom (that come from the Value-sheet and which are correct) and text values that result from the two CROSSTABLE loads. They do not associate and will not work as key values. You will always have problems in your document if you don't fix that one first.

An easy solution would be to reload every table from a CROSSTABLE load into a table.with properly formatted fields. For example, add table names to all LOAD statements. On the Plant tab, add the following to the bottom:

PlantCross:

CROSSTABLE () LOAD...;

:

Plant:

NOCONCATENATE LOAD Date#(Motnh) AS Month, Plant, [Plant Value]

RESIDENT PlantCross;

DROP Table PlantCross;

The formula I gave you in a previous post will now suddenly and magically display a bar chart with the frist few months absent. Like this:

Best,

Peter

[Peter_Cammaert]

Anna Anna,

were you able to fix your document?

Best,

Peter

[#10]

Hi,

PFA,

You need to follow steps to achieve your req.

Regards,

Harshal