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

How to show Month Totals in chart

Hi,

    Here i have requirment like i have to show the month wise tickets inline chart,, how to break the year wise tickets into month wise...

     In DB i have date column there i have 2011 tickets created in a oraganization.

    what i did here is i have return calender  script to showmonth names inX-axis and in expression i took Total count of ticketsbut its notgiving correct result

     one example here is forJan/2011 chartshould show 34000 tickets created but in chart it isshowing only 9tickets,, and in 2011 year total 440000+ tickets  created this count isshowing excatly end of the chart. so how to show month wise tickets in chart  So requesting your help on this.........

                                                       I have uploaded a sample file also please check......

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I believe it is not enough to rename the field T_STMP_PROB_RSLVD, it is a timestamp, so you won't get a match with a CalendarDate, unless for the timestamps that are equal to daystart.

Have you tried using

daystart(T_STMP_PROB_RSLVD) as CalendarDate

?

View solution in original post

8 Replies
swuehl
MVP
MVP

Your ticket table is not linked to your master calendar, so you will get a total for each month.

You could try using

=Count (if(CalendarDate=daystart(T_STMP_PROB_RSLVD), I_TCKT))

to get a count of tickets for each month. But it would be much better to link the tables, like

Directory;

LOAD I_TCKT,

     C_TCKT_STAT,

     T_STMP_PROB_RSLVD,

  daystart(T_STMP_PROB_RSLVD) as CalendarDate

FROM

TIC.xlsx

(ooxml, embedded labels, table is Sheet1);

Not applicable
Author

Hi swuehl thnx for replay i forgot to rename this column T_STMP_PROB_RSLVD in sample file but in my actual file both tables are linked....

                                 I tried with your expression in expresion tab but its giving error could you please eleborate you answer

swuehl
MVP
MVP

I believe it is not enough to rename the field T_STMP_PROB_RSLVD, it is a timestamp, so you won't get a match with a CalendarDate, unless for the timestamps that are equal to daystart.

Have you tried using

daystart(T_STMP_PROB_RSLVD) as CalendarDate

?

Not applicable
Author

swuehl i tried it and  its working perfectly thnx a lot and  we can add multiple expressions also in same chart am i right .....................

swuehl
MVP
MVP

Sure you can add multiple expressions - or have I misunderstood your request?

Not applicable
Author

Your understanrding is perfect...could you please explain about Daystart Function

swuehl
MVP
MVP

Each date or timestamp has a numerical representation, this allows to calculate with dates or timestamps (e.g. calculating the difference of two dates).

For example, today() is 2012-01-02, with a numerical representation of 40910. But now() is a timestamp around 1:26 am of today's date and its numerical representation is 40910.059872685.

The decimal places are specifying the time of day. daystart() ist returning the whole integer, which is equal to 12 am and this value is also used for the date itself.

So if I create a calendar with dates, I get only integer values like 40910 (and the text representation). If I link my timestamp field to my calendar, I won't get a match for any timestamps that are different from 12 am. So I need to convert my timestamps to dates before linking them to my calendar.

And if you are still asking about the daystart function, this is what the Help says:

DayStart(timestamp [, shift = 0 [, dayoffset = 0]])

Returns a value corresponding to a timestamp with the first millisecond of the day contained in timestamp. The default output format will be the TimestampFormat set in the script. Shift is an integer, where the value 0 indicates the day which contains date. Negative values in shift indicate preceding days and positive values indicate succeeding days. If you want to work with days not starting midnight, indicate an offset in fraction of a day in dayoffset, e.g 0.125 to denote 3am.

Examples:

daystart ( '2006-01-25 16:45' ) returns '2006-01-25 00:00:00' with an underlying numeric value corresponding to '2006-01-25 00:00:00.000'

daystart ( '2006-01-25 16:45', -1 ) returns '2006-01-24 00:00:00' with an underlying numeric value corresponding to '2006-01-24 00:00:00.000'

daystart ('2006-01-25 16:45', 0, 0.5 ) returns '2006-01-25 12:00:00' with an underlying numeric value corresponding to '2006-01-25 12:00:00.000'

Hope this helps,

Stefan

Not applicable
Author

Hi Swehl can you please help me on this post......

Hi All,

       I have a intersting requirement i.e. i have to display the data in Hierarchy view(Tree Structure) like

                A

           B         C

        D   E    F  G

Here i  have attached my sample file and xl file also. I have requirement

1)If you select Austrila from list box in Hierarchy structure we dnt havedata for City listboxes for both side then those listboxes shoulddisappear (wheneevr we dnt have the data for any list box that shoulddisapper) and suppose we dnt have data for Region either left or rightside from hierarchy that list box should disappear and the arrow  has directly point to State list box.

2)Based on a Column am dividing the whole structure into 2 parts(left,Right) but in my report am not able to get that correctly am gettingsame data for both sides for this am using a condition like this""=If((Type ='Region') & (LOC ='Left'),Trim(Desc))"".

So please help me to get out of this