Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

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

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!