Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

agrimroquette
Valued Contributor

how to get common month name from two time stamps?

Dear All,

i have two column one is "Creation Date" and another is "Accounted forCapture1.PNG".

i want to get month wise open vs. close tickets in a bar chart, how can i get month name as common from these two fields??

1 Solution

Accepted Solutions

Re: how to get common month name from two time stamps?

Hi,

You can try this script,

LOAD

    "Creation Date" as Date,

    Number,

    month([Creation Date] )as Month,

    Category,

    'Creation' as Flag

FROM [lib://Sample/Sample Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

concatenate

LOAD

    Number,

    "Accounted for" as Date,

    month([Accounted for] )as Month,

    Category,

    'Accounted' as Flag

FROM [lib://Sample/Sample Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Also see attached qvf.

Regards,

Kaushik Solanki

18 Replies
dsharmaqv
Contributor III

Re: how to get common month name from two time stamps?

Hi In the load script you can generate flag, try this

If(Month(Date#(Creation Date, 'MM-DD-YYYY'))=Month(Date#(Accounted for, 'MM-DD-YYYY')),'Flag')

rahulpawarb
Valued Contributor III

Re: how to get common month name from two time stamps?

Hello Agrim,

Trust that you are doing good!

If the Creation Date and Accounted For fields have same month for every records then you can opt for - Month([Creation Date]) AS Month or Month([Accounted For]) AS Month

However, if a record has different month values then you have to have a priority in place i.e. from which field to choose month. In below given example I am selecting Month where it greater in both of fields.

If(Month([Creation Date]) > Month([Accounted For]), Month([Creation Date]), Month([Accounted For])) AS Month

Hope this makes sense.

Regards!

Rahul

sdmech81
Valued Contributor

Re: how to get common month name from two time stamps?

If you can boil down to one date then it would just using month() function.

Else if u expect dash board act as per more than 1 date then,

1) plss check this out Canonical Date

or 2)You can concatenate both date data and then flag acoordingly to use..

sachin

jcampbell474
Contributor III

Re: how to get common month name from two time stamps?

Can you just do two loads (either from the data source or resident) - 1) Creation Date 2) Accounted for.

Add a column in each for the month.  Use month([Creation Date] as monthkey and month([Accounted for]) as monthkey.

Concatenate the tables.

Use monthkey as a Dimension and count distinct either the Number or Category fields.  Or, whatever field you prefer.

Using this will also allow you chart occurrences per category too (if needed).

Re: how to get common month name from two time stamps?

please provide sample application to demonstrate.

thanks

regards

Marco

agrimroquette
Valued Contributor

Re: how to get common month name from two time stamps?

thanks, i am glad to see response from you guysSmiley Happy

i am attaching sample data and .qvf file i hope you can help.

agrimroquette
Valued Contributor

Re: how to get common month name from two time stamps?

Hi Jason,

can you express in detail? i have attached sample file, please see if you can help.

LOAD

    "Creation Date",

    Number,

    "Time Status",

    Recipient,

    "Support Person",

    "Scheduled Start Date",

    "Accounted for",

    Category,

month([Accounted for]  )as Month,

month([Creation Date] )as Month1,

   interval("Accounted for", 'mm'),

Day([Accounted for]) AS Day

FROM [lib://8/GDH_India1.xlsx]

(ooxml, embedded labels, table is Sheet1);

this is the script that i have used

Re: how to get common month name from two time stamps?

Hi,

You can try this script,

LOAD

    "Creation Date" as Date,

    Number,

    month([Creation Date] )as Month,

    Category,

    'Creation' as Flag

FROM [lib://Sample/Sample Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

concatenate

LOAD

    Number,

    "Accounted for" as Date,

    month([Accounted for] )as Month,

    Category,

    'Accounted' as Flag

FROM [lib://Sample/Sample Data.xlsx]

(ooxml, embedded labels, table is Sheet1);

Also see attached qvf.

Regards,

Kaushik Solanki

agrimroquette
Valued Contributor

Re: how to get common month name from two time stamps?

thanks a lot kaushik.solankiit just worked the correct way. i want to know what if the year changes?

because this data is only of 2016, if 2017 data is added than what change will show in chart? and how can i see it as year wise> month wise > creation vs. accounted?