Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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.

14 Replies
sparur
Specialist II
Specialist II

Hi, Herbert.

What about macros?

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.

What about this solution?

Best regards, Anatoly

Not applicable
Author

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!

Not applicable
Author

Hi,

Example:

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

tickets:
load * inline [
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
load
Date,
date(date#(Date,'YYYY-MMM'),'MMM-YYYY') as Date2
resident tickets;

2. Reload and Save it

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



({1<Date2={"$(='<='&Only(Date2)&'>'&Only(date(addmonths(Date2,-12),'MMM-YYYY')))"}> * $<Date2=>}NoOfTickets)

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.

Not applicable
Author

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

Not applicable
Author

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:

count({1<Date2={"$(='<='&only(Date2)&'>'&only(date(addmonths(Date2,-12),'YYYY-MMM')))"}, Type = {'PC'}, AnswerTime = {'Late'}> * $<Date2=>}NoOfTickets)

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


Not applicable
Author

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"

sparur
Specialist II
Specialist II

Hi, Herbert.

Try the following:

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

best regards, Sparur

Not applicable
Author

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.

sparur
Specialist II
Specialist II

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