Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
abc_18
Creator II
Creator II

How to remove some values from dimension field

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

7 Replies
sunny_talwar

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)

PrashantSangle

Hi,

No need to use calculated dimension.

modify your expression like

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

Regards

Great dreamer's dreams never fulfilled, they are always transcended.
sunny_talwar

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)

abc_18
Creator II
Creator II
Author

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 NameCost CenterCCStatusT1 CodeT2 CodeAccount CodeMonthPeriodAmount
AUS148ACTIVE148620410252015-102016-01372
AUS148ACTIVE148620410252015-112016-025778
AUS148ACTIVE148620410252015-122016-063376
AUS148ACTIVE148620410212015-102016-05435375

Here in my report

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

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

sunny_talwar

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

View solution in original post

vsudhakar
Creator III
Creator III

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

abc_18
Creator II
Creator II
Author

Hi Sunny,

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