Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
xyz1
Creator III
Creator III

.

.

1 Solution

Accepted Solutions
kaushiknsolanki

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

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

View solution in original post

15 Replies
dsharmaqv
Creator III
Creator III

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
Specialist III
Specialist III

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
Specialist
Specialist

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
Creator III
Creator III

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).

MarcoWedel

please provide sample application to demonstrate.

thanks

regards

Marco

xyz1
Creator III
Creator III
Author

.

xyz1
Creator III
Creator III
Author

.

kaushiknsolanki

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

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

View solution in original post

xyz1
Creator III
Creator III
Author

.