topic Re: How to hide dimension in QlikView

How to hide dimension

Tue, 10 Mar 2015 13:05:13 GMT

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?

Re: How to hide dimension

Tue, 10 Mar 2015 13:14:44 GMT

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

Re: How to hide dimension

Peter_Cammaert — Tue, 10 Mar 2015 13:33:43 GMT

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.

Re: How to hide dimension

Tue, 10 Mar 2015 13:35:00 GMT

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

Re: How to hide dimension

Wed, 11 Mar 2015 07:13:33 GMT

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 ...

Re: How to hide dimension

Tue, 09 Jun 2015 09:28:59 GMT

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.

Re: How to hide dimension

Tue, 09 Jun 2015 09:35:33 GMT

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.

Re: How to hide dimension

Peter_Cammaert — Tue, 09 Jun 2015 10:45:31 GMT

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

Re: How to hide dimension

Peter_Cammaert — Thu, 11 Jun 2015 09:36:14 GMT

Anna Anna,

were you able to fix your document?

Best,

Peter

Re: How to hide dimension

Wed, 17 Jun 2015 07:10:08 GMT

Hi,

PFA,

You need to follow steps to achieve your req.

Regards,

Harshal