Best Of
Re: How to create a card with data from the calendar month before last
You've got two options.
1) You could use a beast mode to determine if a date is from 2 months ago
CASE WHEN LAST_DAY(CURDATE() - INTERVAL '2' MONTH) = LAST_DAY(`Date Field`) THEN 1 ELSE 0 END
and then just filter on that value being 1.
This method you're always stuck looking at 2 months ago according to the current month. The next option is better in that it will allow you to look at 2 months ago based on any date you have selected in your chart.
2) The more robust option would be to create a custom date dimension table where you have customized offsets (With a report date and a comparison date). You'd then use a Fusion / View to join that dataset to your main dataset based on your date field and the comparison date field. You then can filter for your offset type of 2 months ago. This has been outlined several times here on the dojo. See https://dojo.domo.com/t5/Card-Building/show-percent-on-period-over-period-graph/m-p/50540/highlight/true#M7313.
I highly recommend method 2 as it gives you much more flexibility when processing and displaying your data.
Also @jaeW_at_Onyx has a nice video he put out which outlines this process as well:
Re: How to calculate the total sum of a field in beast Mode
Hi @msharma since you’re wanting to do a conditional sum you need to use a case statement inside your sum to only select the values you want
sum(sum(case when `Transaction` = 1 then `Amount` else 0 end)) over (partition by year(`Posting Date`))
Repeat for transaction 0
Re: Average of tickets by hour question
Hi @user030156 / Michael
I'd recommend using a dataset view (talk to your CSM to join the beta if you don't already have it) to calculate the date and hour of your timestamp.
Date:
DATE(`Timestamp`)
Hour:
HOUR(`Timestamp`)
Then group based on these two fields (date and hour) and count the number of tickets you have using the COUNT aggregate within your dataset view.
Then using the dataset output from this view in your card you can use the Hour field as your X axis and then take the AVERAGE of your count field to get the average per hour over the last X days based on your card filter.
Re: Set goals in ETL
APPEND your data instead of JOINing it.
Re: Help graphing a calculated field that sums up two data columns
If your beast mode is calculating correctly in the pivot table, you can use that same beast mode as the y axis in the bar graph and the sales date in the x-axis and then use the date range filter in the top right to tell it to graph by month.
Re: Fill down values in a column in DOMO ETL
to fill down data. for each column you want to track each time the row changes.
so you need
Prev_Value (use a LAG 1)
then is_Change (case when Prev_Value != value then 1 then 0
then cum_sum of isChange
in your previous example you should have 1 all the way from Text 1 to Text 2, then from Text 2 onward should be a 2 up until Text 3...
at this point you can JOIN (SELECT * FROM ... WHERE is_change = 1) to your BaseTable
On cum_sum = cum_sum. this will spread VALUE across each row in BaseTable for each cum_sum group.
Then rise wash and repeat for each column. This solution will be easiest in Magic 2.0 or Redshift but probably not in MySQL.
If you're doing it in MySQL you actually can do this with fewer steps user-defined variables and a case that matches on if the value changed or not.
Re: WB5 time settings
There doesn't appear to be a way to choose your timezone. However, if you click on Advanced Schedule, It will show the time in the timezone your computer is set to next to the UTC time that you can edit.
Re: Lag Window Functions gave me split rows
lag(sum(case when `control or target` = 'target' then `vol_usd` else 0 end)) over (partition by year(`trade_date`)*100 + month(`trade_date`))
I think Domo broke / changed functionality.
You can no longer use an expression in the partition or order by clause -- year(`trade_date`)*100 + month(`trade_date`)
add this to your dataset as a hardcoded column
Re: Cannot Move page to subpage with Admin Access
I have struggled with this at times as well. I assume you are trying to move these pages through Admin - Pages. What is not immediately clear when you are viewing the pages listed on this page is that it doesn't show you all the pages just by scrolling. In order to move pages not initially shown, you need to find them by using the filter feature.
As shown, click on the 3 dots and then start typing the name of the page and then click Apply. You should see your pages that you are looking for.
Let me know if this fixes your issue or if you have something else going on.