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: 
1 Solution

Accepted Solutions
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
xyz1
Creator III
Creator III
Author

.