Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
stabdha91
Contributor III
Contributor III

Monthly data into weekly and daily.

Hi all,

I have a situation where the data is on monthly basis but i want to calculate it on weekly and daily basis.

**Data in attached sheet.

I want to create bar chart for 'Received'/'Resolved'/'Rollover'/'Inventory'

Received :  No. of tickets received monthly/weekly/daily

Resolved : No. of tickets resolved monthly/weekly/daily

Rollover : No. of tickets rolled to next month/week/day

Inventory: No. of tickets carry forwarded to next month/week/day

For Monthly basis i am getting correct data for Received/Resolved/Rollover/Inventory but not for weekly and daily.

1 Solution

Accepted Solutions
stabdha91
Contributor III
Contributor III
Author

For this what i did is :

1.) created first_rolling_day

2.) created first_rolling_week

3.) created first_rolling_month

4.) created days_between_rolling

5.) created week_between_rolling

6.) created month_between_rolling

and then in script:

1.) marked all tickets with open_date as OPEN

2.) marked all tickets with close_date as CLOSED

3.) marked tickets as Rolling where first_rolling_day/week/month > base_date(01/05/2016) running an iternation as

iterno() <= month/week/days_between_rolling.

View solution in original post

11 Replies
MindaugasBacius
Partner
Partner

Connect open_date field with master calendar to achieve your targets.

kaushiknsolanki

Hi,

You need to tell us what have you done for Monthly calculation.

If possible share the QVW file which shows your monthly calculation.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
stabdha91
Contributor III
Contributor III
Author

For monthly i have created some flag.

openFlag for all tickets where open_date >01/05/2016

CloseFlag for tickets which have close_date >01/05/2016

RollingFlag for tickets which have first rolling month > 01/05/2016

In etl i am calculating first_rolling_month., first_rolling_week, first_rolling_day.

I can't share the qvw

stabdha91
Contributor III
Contributor III
Author

But how i determine while tickets will go under rollover and inventory from master calendar

HirisH_V7
Master
Master

Hi,

Check

May be like this,

Data:

Load*,

close_date- open_date as Diff,

If(close_date- open_date>30,'MonthCount',

    If(close_date- open_date>7,'WeekCount',

        If(close_date- open_date>1,'dayCount'))) as Ticketstatus

;

LOAD Ticket,

     open_date,

     DayNumberOfYear(open_date) as OpenDayNumber,

     Month(open_date) as OpenMonth,

     Week(open_date) as OpenWeek,

     DayNumberOfYear(close_date) as closeDayNumber,

     Month(close_date) as closeMonth,

     Week(close_date) as closeWeek,

     close_date,

     status

FROM

[Servicedesk.xlsx]

(ooxml, embedded labels, table is ServiceDesk);

At front end,

is this what you require,

Monthly Weekly Daily-231725.PNG

else can you please elaborate your requirement.

PFA,

HTH,

Hirish

HirisH
“Aspire to Inspire before we Expire!”
gupta_n8
Specialist II
Specialist II

Hope Something Like this will help you out.

stabdha91
Contributor III
Contributor III
Author

and how to determine rollover and inventory tickets?

gupta_n8
Specialist II
Specialist II

what will be your formula??

MarcoWedel

Hi,

maybe one solution could be:

QlikCommunity_Thread_231725_Pic1.JPG

QlikCommunity_Thread_231725_Pic2.JPG

QlikCommunity_Thread_231725_Pic3.JPG

QlikCommunity_Thread_231725_Pic10.JPG

QlikCommunity_Thread_231725_Pic11.JPG

QlikCommunity_Thread_231725_Pic9.JPG

QlikCommunity_Thread_231725_Pic4.JPG

QlikCommunity_Thread_231725_Pic5.JPGQlikCommunity_Thread_231725_Pic6.JPG

QlikCommunity_Thread_231725_Pic7.JPGQlikCommunity_Thread_231725_Pic8.JPG

tabTickets:

LOAD Ticket,

    open_date,

    close_date,

    status

FROM [https://community.qlik.com/servlet/JiveServlet/download/1118611-244247/Servicedesk.xlsx] (ooxml, embedded labels, table is ServiceDesk);

tabTicketDates:

LOAD *,

    -(not(opened or closed)) as ongoing;

LOAD Ticket,

    Date,

    AutoNumberHash128(Ticket,Date) as %TicketDateKey,

    -(Date=open_date) as opened,

    -(Date=close_date) as closed;

LOAD *,

    Date(open_date+IterNo()-1) as Date

Resident tabTickets

While open_date+IterNo()-1 <= RangeMin(close_date,Today());

tabTemp:

CrossTable (DateStatus, StatusValue)

LOAD %TicketDateKey,

    opened,

    closed,

    ongoing

Resident tabTicketDates;

tabTicketStatus:

LOAD %TicketDateKey,

    DateStatus

Resident tabTemp

Where StatusValue;

hope this helps

regards

Marco