Hi,
i’m currently trying to count some data points by custom quarters which differ from regular calendar ones. For instance I’d like to determine Q1 as Dec/Jan/Feb etc. I’ve tested periodToDateCount function and it’s working fine for regular quarters.
Is there any tested solution to adjust quarters as per my preference?
For Custom date hierarchy , it would be best practice to create Date Hierarchy table and define your custom quarters. Join this Date hierarchy dimension table with your fact/transaction table where you have data points .In your visual rollup based on defined field which has custom quarter. You should be able to aggregate at required level.
Thanks @Neeraj thanks for your inputs. What do you mean by Date Hierarchy table? Could you share some example how to use it on sample date?
Upon considering it will be struggle to join csv file to my dataset because missing key in the file. Was thinking of such formula so I can determine current month and do counts accordingly. However such formula isn’t accepted by QS due to syntax issue. If this would work i could add conditions for other months and filter last M on visual:
ifelse
(
extract('MM',now()) = 1,
distinct_countIf({Id}, extract('MM',{upload date}) = 1)),
"Not applicable"
)
Thanks.
Hi @Rad ,
By Date hierarchy I meant, you can create simple date table in your database using standard SQL with sequence of dates that cover the entire date period required for the analysis with fields such as day of the week, workdays, weekends, quarters, months, years, or custom quarter you would like to define. Here is quick look at it.
You can join it with your main table which hold all your transactions like the one i am doing with sales and use this dataset for analysis
I have one example here to take a look at -
Custom Quarter
You can copy analysis by clicking on the copy icon on left pane of this sample dashboard.
In case you need further assistance with your problem, please create a sample dashboard with sample dataset showing your problem state using Arena and post it here.(Details on using Arena can be found here - QuickSight Arena )
Yes, joined on dim_date.
so i’ve generated a sample csv file with custom quarters as below but when joined “date” to “upload date” i’m not getting any values for custom quarter, any hints? Should data set be generated by SQL? As per my understanding the output should be same regardless csv/sql source.
csv:
QS output:
Please make sure data type for date and upload date is matching in two datasets you built.
Can you also try using this calculated field , replacing Order date with your date in consideration ?
concat (‘Q’ , toString( extract ( ‘Q’, addDateTime(1, ‘MM’, truncDate(‘MM’, {Order Date})))) , ’ ', toString(extract(‘YYYY’, addDateTime(1, ‘MM’, truncDate(‘MM’, {Order Date})) )) )
This creates custom Quarter attribute considering each date and keeping in mind logic you mentioned - Dec /Jan /Feb is Q1 .
In case you have just one custom field this would serve your needs .
Both are dates datatypes, do date formats matter?
It has to be same date format , have you given a try with calculated field i shared ?
concat ('Q' , toString( extract ( 'Q', addDateTime(1, 'MM', truncDate('MM', {Order Date})))) , ' ', toString(extract('YYYY', addDateTime(1, 'MM', truncDate('MM', {Order Date})) )) )
Jut replace Order Date with your “upload date”
