Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
bi_analysts_2
Creator
Creator

Limiting Dimensions Values

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)

limit dimension values.PNG

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

1 Solution

Accepted Solutions
sunny_talwar

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:

Capture.PNG

View solution in original post

11 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
sunny_talwar

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:

Capture.PNG

View solution in original post

bi_analysts_2
Creator
Creator
Author

Hi Sunny,

thanks for your help but something weird happens:

limiting dimension values.PNG

the data begins even before the November 2015

bi_analysts_2
Creator
Creator
Author

Hi Gysbert,

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

limiting dimension values 1.PNG

Thanks,

Tal

sunny_talwar

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')

bi_analysts_2
Creator
Creator
Author

Hi Sunny,

After inserting what you wrote me:

limiting dimension values2.PNG

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

i received the following:

limiting dimension values.PNG

bi_analysts_2
Creator
Creator
Author

Hi Sunny,

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

have any idea?

Thanks,

Tal

sunny_talwar

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

bi_analysts_2
Creator
Creator
Author

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:

Qlik version.PNG


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