- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
.
.
- « Previous Replies
-
- 1
- 2
- Next Replies »
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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).
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
please provide sample application to demonstrate.
thanks
regards
Marco
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
.
- « Previous Replies
-
- 1
- 2
- Next Replies »