POP Calculation and Auto Filter using Beast Mode

user03595

Hello Everyone,

 

I am new with Domo and nice to meet you all. Actually, I dont have any experience with this type of platform and have limited knowledge in SQL, ETL, and Beast Mode.

 

I am currently trying to create POP calculation and auto filter using beast mode from aggregated daily numbers attached below. I have tried some beast mode code but didnt work and stuck for few days already.

 

POP Calculation

I am trying to calculate DoD, WoW, and MoM change from the attached file but couldnt get the perfect code to calculate the difference and %difference from the data I have. I have read this docs and this link but couldnt get anything except yesterday data only and couldnt use the data to calculate % difference between today and yesterday.

Basically, I would like to calculate the difference and % difference like below screenshot. How can i get it using beast mode?

 

 

Auto Filter

I do understand that DOMO card can change the card by graph (Daily, Weekly, and Monthly) and this is a really great function. Then I dont need to have multiple dataflow (Daily, Weekly, and Monthly).

 

However, since my data is aggregated daily, the chart doesnt show correct numbers for WoW and MoM. So, my logic is to add new filter "Last Day" to get last day of the month and "Day of Week (Sunday)" to get Sunday data for weekly (Mon-Sun).

Then everytime the users change to weekly the filter will automatically change to Sunday, and when users change to monthly the filter will automatically change to Last Day.

Can we change the filter automatically based on the choosen graph? Is there any better approach than this one?

 

Thanks and truly appreciate for any help here.

WK

BlueRooster

Have you tried using the Period over Period card? Is there a reason you're trying to do this manually?

 

Take a look here: http://knowledge.domo.com?cid=buildingpop

 

If that won't work for you, can you explain a bit more about the final result you're looking to acheive? Are looking to create 1 or card or multiple?

 

Sorry for all the questions ?

Valiant

user03595

Hi Valiant,

 

Thanks for your reply and sorry for not explaining well.

 

Have you tried using the Period over Period card? Is there a reason you're trying to do this manually?

 

Yes, I just tried POP card as you suggested. The result was almost similar to my needs but it only shows previous day data only not daily %change. Is it possible to show %change too?

 

Basically, I would like to create 1 card that shows aggregated sales as bar chart and % change as line chart and can change the graph by daily, weekly, and monthly like below results.

* %Change can be minus due to return order

 

Final Results

 

 

 

The reason I need to do this manually because my data is daily aggregated I need to add additional column to do the filter for Monthly and Weekly (Please see above screenshot)

Monthly - use 'Last Day of The Month'

Weekly - use 'Sunday' because I am looking at Monday-Sunday

 

If not the sales numbers will show wrong numbers because DOMO sum the numbers to get Weekly /Monthly like below example for weekly

 

 

Then if I change Sales into No Aggregation (because my data already aggregated) this happens

 

 

 

Basically, I would like to

1. Calculate %change from my daily aggredated data and then

2. Create 1 card that can show Daily, Weekly, and Monthly Total Sales and %Change by changing "Graph By" like below

 

 

Sorry if it is a long reply and i hope you dont get confuse with it. I am not sure if thats the better approach because thats what I did on excel.

 

I truly appreciate your help

 

Thanks

WK

 

Note:

* I uploaded excel file to show you final results and the problems I have as an example because I cant even calculate %change on DOMO