how to set up filter for the last day of data set in the new text card for summary

WizardOz
WizardOz Contributor
in Charting

I want to use this new text card to insert the summary numbers.  It worked fine with most scenario, but I am have difficulties to report the last date and to set up the filter to get the last date visits of my dataset. 

for example, 3/31/2020 is the last date in my dataset, then I want to show: 

"On 3/31/2020, total 5023 visits" 

How to set up the date filter so that it aways pick up the last date of the dataset?

 

Thank you.

 

Olivia

 

 

 

 

Comments

  • Cartergan
    Cartergan Contributor

    You could try a beastmode using MAX(Date) and it should always select the last date in the dataset. 

  • marisaborges
    marisaborges Member

    Hi!

    The beastmode with MAX(date) will change its value every day and will require you to change the value to be filtered every day.


    You should try in the Dataflow to create a new column to use as a filter. For example,

     

    CASE 
        WHEN date = MAX(last_date) THEN 1
        ELSE 0
    END AS is_last_date

    Then filter when is_last_date = 1

  • Cartergan
    Cartergan Contributor

    Was the goal not to show the Last date in the data set everyday? Which means it should be updated everyday. 

  • marisaborges
    marisaborges Member

    according to the attachment no, I think the goal is to filter by the last date

  • WizardOz
    WizardOz Contributor

    Max(date) is the first thing I did, but unfortunately, the new textbox feature does not show this max(date) in the filter section

  • WizardOz
    WizardOz Contributor

    @marisaborges thank you for your idea.  But I am not sure it is doable in the dataflow - this dataset has 120 million rows and 200 columns, in order to reduce the run time, I had to run the whole dataflow for 2018, 2019, and now just run dataflow for 2020 and append to the 2018 and 2019 dataset using data assembler.  Not sure how to work it out.

     

     

This discussion has been closed.

Categories