topic Re: How to remove some values from dimension field in QlikView

How to remove some values from dimension field

abc_18 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have one dimension field called period, and data is like

Period
2015-05
2015-06
2015-07
2015-08
2016-03
2016-04
2016-05
2016-05

need to show record only till April(2016-04).

This report is getting generated monthly manually in excel and requirement is that, current month record should not come ie May data(2016-05).

How to achieve this by using calculated dimension??

Re: How to remove some values from dimension field

sunny_talwar — Wed, 04 May 2016 11:45:06 GMT

May be like this:

If(MonthStart(Date#(Period, 'YYYY-MM')) <= MonthEnd(Today(), -1), Date(MonthStart(Date#(Period, 'YYYY-MM')), 'YYYY-MM'))

UPDATE: I have used excessive formatting just because I was not sure if your Period was read as Date or Text. If you know it is read as date, then you might be able to simplify to this:

If(Period <= MonthEnd(Today, -1), Period)

Re: How to remove some values from dimension field

PrashantSangle — Wed, 04 May 2016 11:49:23 GMT

Hi,

No need to use calculated dimension.

modify your expression like

sum({<Period={"<=$(=Date(AddMonths(Today(),-1),'YYYY-MM')"}>}Sales)

Regards

Re: How to remove some values from dimension field

sunny_talwar — Wed, 04 May 2016 11:50:57 GMT

Great idea . But I guess this will not allow for selections. May be like this to allow for selections

Sum({<Period *= {"<=$(=Date(AddMonths(Today(),-1),'YYYY-MM')"}>}Sales)

Re: How to remove some values from dimension field

abc_18 — Wed, 04 May 2016 12:10:49 GMT

Hi Sunny,

In my report after applying this in calculated dimension

If(Period <= MonthEnd(Today, -1), Period) only that column is coming as blank, but requirements is like entire row record should not appear.

3rd and 4th row data should not come in report.

Vendor Name	Cost Center	CCStatus	T1 Code	T2 Code	Account Code	Month	Period	Amount
AUS	148	ACTIVE		148	62041025	2015-10	2016-01	372
AUS	148	ACTIVE		148	62041025	2015-11	2016-02	5778
AUS	148	ACTIVE		148	62041025	2015-12	2016-06	3376
AUS	148	ACTIVE		148	62041021	2015-10	2016-05	435375

Here in my report

Date(MonthStart(worked_date,3),'YYYY-MM') is as Period,

and worked date is date field like 01/02/2016

Re: How to remove some values from dimension field

sunny_talwar — Wed, 04 May 2016 12:24:45 GMT

You need to check 'Suppress When Value Is Null' on the dimension tab for this particular dimension. Try that and I believe it would fix your issue

Re: How to remove some values from dimension field

Anonymous — Wed, 04 May 2016 13:14:30 GMT

if you want display the data current month-1 so that

in edit script at dimension if(period<>'(max(date))-1'as period

try this one

Re: How to remove some values from dimension field

abc_18 — Thu, 05 May 2016 04:46:34 GMT

Hi Sunny,

Ya it's working now..Thanks a lot!!