14 Replies Latest reply: Apr 29, 2009 10:06 PM by Karol Dorniak

# Set Analysis - dynamic chart over the last 12 months

Hello,

I need some help in Set Analysis. I searched in the forum for a solution of my problem but nothing works for me.

Problem description:

Fields: 1) Ticketnumber (ex. 5, 14, 1265, 3459......)

2) YearMonth (ex. 2006-Jan, 2006-Nov, 2007-Jul, 2009-Mrz ....) ==> format is 'YYYY-MMM'

Endresult:

I select only 1 value in the listbox "YearMonth" for example "2009-Jan" I would like to have a bar chart which shows me 12 bars, from "2008-Feb" to "2009-Jan", for each YearMonth one bar. As dimension in the barchart I have "YearMonth" and as Formula I would like to have a count(Ticketnumber).

The selection should be flexible I always select only 1 value in the field "YearMonth":

Selection: "2008-Sep" ==> so the bar chart includes the date range 2007-Okt to 2008 Sep

I hope the information is enough that somebody could help me with a set analysis formula! Thanks.

• ###### Set Analysis - dynamic chart over the last 12 months

Hi, Herbert.

You can write a macro, that would have selected the correct 12 value in the YearMonth.

For example: Selection: "2008-Sep" ==> so the 'YearMonth' Field will be includes the date range 2007-Okt to 2008 Sep.

Best regards, Anatoly

• ###### Set Analysis - dynamic chart over the last 12 months

A macro is not the solution for what I am searching for, because I am not very good with macro writing.

Anatoly maybe you have a Macro code for my example and how I can use this in my chart formula?

Has anybody else has a solution with the use of set analysis?

Thanks!

• ###### Set Analysis - dynamic chart over the last 12 months

Hi,

Example:

1. create new QVW file and copy this script to Edit Script:

tickets:
Date, NoOfTickets

2006-Jan, 10
2006-Feb, 30
2006-Mar, 34
2006-Apr, 40
2006-May, 70
2006-Jun, 12
2006-Jul, 33
2006-Aug, 20
2006-Sep, 35
2006-Oct, 40
2006-Nov, 60
2006-Dec, 43

2007-Jan, 20
2007-Feb, 30
2007-Mar, 40
2007-Apr, 40
2007-May, 43
2007-Jun, 54
2007-Jul, 23
2007-Aug, 33
2007-Sep, 55
2007-Oct, 66
2007-Nov, 43
2007-Dec, 39

2008-Jan, 74
2008-Feb, 82
2008-Mar, 65
2008-Apr, 54
2008-May, 43
2008-Jun, 32
2008-Jul, 34
2008-Aug, 56
2008-Sep, 72
2008-Oct, 37
2008-Nov, 52
2008-Dec, 24

2009-Jan, 50
2009-Feb, 45
2009-Mar, 44

];

//i am creating a proper date field
left join
Date,
date(date#(Date,'YYYY-MMM'),'MMM-YYYY') as Date2
resident tickets;

3. Create a Listbox for field Date 2. In the properties check: "Alwasys one selected value".

4. Create Bar chart -

a. dimension: Date2

b. expression:

Sum

c. sort it by Date 2 asc.

Enjoy:) If you let me know how can I attached QVW to this post, I can give you the example.

• ###### Set Analysis - dynamic chart over the last 12 months

Hi,

Karoldorniak, thank you very much great work. The set analysis expression works perfectly for me. I can need this also in other applications.

Great job and thanks everybody for the help.

Kind regards

Herbert

• ###### Set Analysis - dynamic chart over the last 12 months

Hi,

I have another question to the community to the above problem, the set analysis question works fine thanks, but it is missing something.

I have modified the set analysis funktion a little bit with some necessary restrictions. Here is an example:

Explanation:

This function is used in a bar chart with date 2 as dimension, the user selections always only 1 YearMonth for example 2008-Nov and gets as result a bar chart with 12 bars from "2007-Dez" to "2008-Nov" always 12 months. The modification is, that it shows only results with Type = 'PC' and where the String is 'Late' in the field AnswerTime.

Problem with the expression:

The expressions do not show bars in the chart where the result is null, in the example where exists no Tickets in the month. So if from the 12 months, there are 2 months where no tickets exists, the chart show only 10 months, but I also need the bars with the null values = without tickets.

There is no possibility to check something in the Proberties of the chart because it is set analysis but I am not sure, I have not found something.

I hope somebody could help me?

Thanks

• ###### Set Analysis - dynamic chart over the last 12 months

Try this:

In the chart's Properties window....

Presentation tab: uncheck Suppress Zero-Values

If that doesn't work, also try this:

Dimensions tab: check "Show All Values"

• ###### Set Analysis - dynamic chart over the last 12 months

Thanks for the answers, but I have tried both before, to uncheck the Suppress Zero-Values does not work with set analysis funktions

and the other possibility to check in the dimensions tab "Show All Values" works but that overrides me the set analysis function, and shows me not only 12 bars in the chart, when it is checked it shows me all bars for every YearMonth in the field "Date2" for example every form 2005-Okt to 2009-Apr. I think there must be a solution in the set analysis expression itself, I know it is difficult problem.

• ###### Set Analysis - dynamic chart over the last 12 months

It's very strange.

because I just made an example posted by karoldorniak and everything works. After I turned off the suppression of zeros(Presentation Tab) my chart shows, all 12 bars with 0 values.

see in attachment

• ###### Set Analysis - dynamic chart over the last 12 months

Thanks for the application, I think there is a tricky problem, when I opened it, yes it is true that it shows 12 bars, that ok, but when I select for example Dez-2007 than it should show the hole year, but there are 2 null values in the month March and July and there the bar shows completely different values, take a look at the screenshots, I have change nothing.

Than I have tested it with my basic setting in the script, and overrided with mine.

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='YYYY-MM-DD hh:mm[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';

And there was again my old problem with the missing 2 bars, March and July with the 0 values where missing.

Very strange, I think I test the application with a other version of QlikView and than I report here again.

• ###### Set Analysis - dynamic chart over the last 12 months

Herbert,

try to make a reload. Once again I have updated(reload) and save, it feels good.

• ###### Set Analysis - dynamic chart over the last 12 months

I reinstalled QlikView once again, and now it shows me the correct values, also the 0 values, very strange, I cannot explain it why there where first wrong values. Thanks for the .qvw

Now I will try it with the data of my real application and if it also works when I include some modifications in the set analysis set. Maybe there is an error also in the basic skript somewhere. Thanks for the moment!

• ###### Set Analysis - dynamic chart over the last 12 months

That's good!

• ###### Set Analysis - dynamic chart over the last 12 months

Hi Herbert,

Recently I had problem with a complex expression on the QV 8.5 wich wasn't the newest 8.5. After upgrading it to the most recent version, the expression started working correctly...

Cheers

Karol

• ###### Set Analysis - dynamic chart over the last 12 months

Hi, Herbert.

Try the following:

Properties bar chart -> Presentation Tab -> Zuppress Zero-Values (Uncheck)

best regards, Sparur