Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am creating a Line Chart on QlikView, I am using a field that will have the date as the following: 2016-09, 2016-08, ..., 2014-01
The problem is that I will like to display on the chart only the last 6 months, for this I am trying to add a calculated dimension for this field, but it's not working.
When I used just the field, all months are being displayed from the last 2 years, I tried to add an additional filter as =YearMonth >'2016-02', but is not working, if I removed the field and leave only this calculated dimension the line chart only displays one value.
What can I do to fix this?
Hello Roberto,
Not sure how you are loading data, here is an example of resolution:
First data load (I am making sure that field is laoded as date)
LOAD
Date#(Date,'YYYY-MM') as Date,
Value
;
LOAD * INLINE [
Date, Value
2016-10, 1
2016-09, 1
2016-08, 2
2016-07, 3
2016-06, 1
2016-05, 6
2016-04, 7
2016-03, 9
2016-02, 3
2016-01, 4
];
Then I am adding calculated expression in chart:
=IF(Date>=AddMonths(MonthStart(Today(1)),-6), Date)
Also marking "suppress when value is null"
You can also play around with "Dimensions Limits" tab.
Adding sample file for you.
BR,
Kuba
Hi,
First convert your month field to a valid date format in script like below
DAta:
LOAD
*,
MonthName(DateFieldName) AS Month;
LOAD
Date(Date#(DateFieldName, 'YYYY-MM')) AS DateFieldName,
Value
FROM DataSource;
Now in set analysis try like below
Last 6 Months Sales
Sum({<DateFieldName={‘>=$(=MonthStart(Max(DateFieldName), -5))<=$(=Date(Max(DateFieldName)))’}>} Sales )
Check below link for similar kind of expressions for various scenarios.
Set Analysis for Rolling Periods
Regards,
Jagan.
Hi,
I don't have problems with the field, as it's already with values as 'yyyy-mm' what I need is to create a line chart for last 6 months, and here is where I have problems. As I will like to have this filter only for one dashboard I am not being able to create it.
Is there a way to create a filter for this specific request. I tried in the a calculated dimensions, or with the limits, but still not able.
I'll appreciated your help
Hi,
I think the Month is in the string format, so you have to convert to date to apply filters in set analysis.
Regards,
Jagan.
Then, how can I convert that, when the data is already imported and used in another sections?
Hi,
Convert the field in script and derive a new column, and use this new column in ur set analysis.
Regards,
Jagan.
Sure, I will do that, can you please help me with the steps to convert the field?
Hi,
Try this
DAta:
LOAD
*,
MonthName(DateFieldName) AS Month;
LOAD
Date(Date#(DateFieldName, 'YYYY-MM')) AS DateFieldName,
Value
FROM DataSource;
Now in set analysis try like below
Last 6 Months Sales
Sum({<DateFieldName={‘>=$(=MonthStart(Max(DateFieldName), -5))<=$(=Date(Max(DateFieldName)))’}>} Sales )
Check below link for similar kind of expressions for various scenarios.
Set Analysis for Rolling Periods
Regards,
Jagan.
I have tried the load in the script section but it failed. I have tried the following:
Sum({<DateFieldName={‘>=$(=MonthStart(Max(DateFieldName), -5))<=$(=Date(Max(DateFieldName)))’}>} Sales )
In the DateFieldName I used the YRMON field that already has the 'YYYY-MM' data, but don't know what "Sales" means or where comes from, so I was not able to use it.
I have create filters that are being set automatically when I hit on a text box, something like this:
The dashboards are being filtered by this, when I hit on the text box. This is how the properties of the dimensions look like:
Now, I have try the same dimension on the specific dashboard, but it's not doing anything. Is there any way to create a filter in each dashboard that won't affect other ones? that is what I am looking for. Something similar to the second picture but that works.
Thank you in advanced for your help.