topic Re: Limiting Dimensions Values in QlikView

Limiting Dimensions Values

Anonymous — Mon, 09 May 2016 14:27:04 GMT

Hi,

Hope the following will make sense.

I am using a line chart in order to present count of actions by their Monthname, current definition:

Dimension: Monthname(date)
Messure: Count(date)

but the full range of dates is not relevant, i am interesting that the visualization will contain only the last 6 months.

i want to limit this already in the set of the visualization.

imagine that i have the following column:

date

03/02/2016

06/05/2016

09/05/2016

02/03/2016

06/01/2016

22/05/2015

15/11/2015

since today is 09/05/2016 i am not interested to see all the values of the dimension, only of the last 6 months (only from November 9th 2015 and afterwards..)

Thanks!

Tal

Re: Limiting Dimensions Values

Gysbert_Wassenaar — Mon, 09 May 2016 15:59:52 GMT

Try this measure: Count({<date={">$(=Date(AddMonths(max(date),-6),'DD/MM/YYYY'))"}>}date)

Re: Limiting Dimensions Values

sunny_talwar — Mon, 09 May 2016 20:41:04 GMT

Or may be this if you can have dates beyond today for any reason:

=Count({<date = {"$(='>' & Date(AddMonths(Today(), -6), 'DD/MM/YYYY') & '<=' & Date(AddMonths(Today(), 0), 'DD/MM/YYYY'))"}>} date)

Where this -> ='>' & Date(AddMonths(Today(), -6), 'DD/MM/YYYY') & '<=' & Date(AddMonths(Today(), 0), 'DD/MM/YYYY') in a text box object give this:

Re: Limiting Dimensions Values

Anonymous — Tue, 10 May 2016 07:15:57 GMT

Hi Sunny,

thanks for your help but something weird happens:

the data begins even before the November 2015

Re: Limiting Dimensions Values

Anonymous — Tue, 10 May 2016 07:57:35 GMT

Hi Gysbert,

Thanks for your help but for some reason it displaying as following and not from November 2015:

Thanks,

Tal

Re: Limiting Dimensions Values

sunny_talwar — Tue, 10 May 2016 16:51:57 GMT

When you add this to a text box object, what range do you see?

='>' & Date(AddMonths(Today(), -6), 'DD/MM/YYYY') & '<=' & Date(AddMonths(Today(), 0), 'DD/MM/YYYY')

Re: Limiting Dimensions Values

Anonymous — Wed, 11 May 2016 06:59:30 GMT

Hi Sunny,

After inserting what you wrote me:

(*[initial insert date] represent the date field/dimension.)

i received the following:

Re: Limiting Dimensions Values

Anonymous — Sun, 15 May 2016 12:08:35 GMT

Hi Sunny,

for unknown reason now the line chart is displaying an empty chart..

have any idea?

Thanks,

Tal

Re: Limiting Dimensions Values

sunny_talwar — Sun, 15 May 2016 18:15:37 GMT

Tal -

It is difficult to say why the chart works some day and doesn't work other day. I guess the question to ask you would be, is there something that is changing? I won't know because I have never seen your application. I would request you to may be provide more details to help you better

Best,

Sunny

Re: Limiting Dimensions Values

Anonymous — Mon, 16 May 2016 07:02:09 GMT

Hi Sunny,

First i am very appreciating your will to resolve my issue!

second, i will try to be as specific as possible:

-I would like to create a simple Line Chart

-imagine that i have a column ( column name: [initial insert date] ) of date values, format: dd/mm/yyyy

-the dimension would be: MonthName([initial insert date])

-the measure would be: count of [initial insert date] --> IF the value is from the last 6 months

in addition, is there a work around possible not utilizing set analysis?

(although i prefer to learn how to use set analysis properly.. already seen both videos Michel Tarallo published)

*it might be a problem of not upgrading yet to Qlik sense to latest version? currently in use:

mto‌ your help will be much appreciated since my boss is pushing for a solution and i want to be dead already!

Re: Limiting Dimensions Values

Anonymous — Tue, 17 May 2016 12:48:39 GMT

hi Sunny stalwar1 ,

please check the attached sample file and let me know if this works out using this data set..

=Count({<date = {"$(='>' & Date(AddMonths(Today(), -6), 'DD/MM/YYYY') & '<=' & Date(AddMonths(Today(), 0), 'DD/MM/YYYY'))"}>} date)

Thanks!

Tal

Re: Limiting Dimensions Values

Anonymous — Mon, 23 May 2016 08:19:38 GMT

Hi,

the problem was with the date format:

instead of DD/MM/YYYY, it was MM/DD/YYYY

thanks!