Dynamic filter to find maximum value within a CASE statement

user06058
user06058 Member
in Archive

I'm having an issue with trying to generate a dynamic comparison that can be filtered. What I want to do is to have a series of multiple updates to the dataset where I can pick two dates when updates occured and then see the difference between them (largest gains or losses). Each update has a unique integer identifier in a seperate column in the dataset.

I was thinking of using a beastmode like the following:

 

SUM(CASE WHEN `Update Number` = ceiling(`Update Number`) THEN `Revenue` ELSE 0 END)

 

However both the ceiling and max functions are resolving for individual rows and not for the overall dataset.
Due to the need to filter the data and the high number of updates, solving with SQL in the dataset is also not an option.

Any thoughts or suggestions?


Thanks
Andy

Comments

  • rado98
    rado98 Contributor

    Hi 

    Beastmodes cannot handle a combination of aggregate and non-agregate function. They will either look at individual rows or a set of rows.

    I find this to be a key handicap of domo vs say Excel.

    You need to get the ceiling value using ETL and then compare in the card

  • user06058
    user06058 Member

    I understand that using aggregate and non-aggregate functions in the same row doesn't work in a beastmode. Unfortuantely I can't use an ETL for the ceiling function as it has to be dynamic allowing the user to pick from a series of updates and that will set the ceiling as well as the floor and report the delta between them. I"m just hoping someone has a brilliant way of accomplishing this. A longshot but worth a try.

Categories