Hi
I hope some one can help me.
I have a excel file, in the excel file consists all the order there are bought through the shop.
I will showing how many order each customer has bought the last month.
And I use Groupby so i can see how many order every customer have order. And its work fine
But how can I only show order from the last 30 days. Now i showing all the order from the last year, but i will only showing the last 30 days.
Can klipfolio do that ? :-D
Hey Martin,
Thanks for reaching out! :) We most certainly can! I would actually like to help you take a step back and show you how you can create this table without the need to use formulas. What you can use is our applied actions.
Step 1:
In your table, you'll have two columns, just as you have in your screenshot. But instead of using GROUP and GROUPBY, you just need to reference your columns - so Firma in the right column and Ordrenr respectively.
Step 2:
Now you can Group the left column for Customer using the GROUP action:
This will group your customers so it will remove all your duplicates and each customer only shows once.
Step 3:
Choose the aggregation type for your Total Order column. In this case, you're counting the order numbers.
Step 4:
So you should now be where you were in your screenshot above. BUT, without formulas! Now the next part is the Date Filter that you wanted to add. To do that, add a Hidden Data component and you can name it Date Filter. Rerence your date column Dato, klokke. This will automatically be detected as a date. If it isn't you can ensure it under the Properties Tab:
Now you just need to filter for the last 30 days:
And that is pretty much it! You're all done!
Thanks its work, its easy when you know it THANKS
Hi all, this makes total sense, thanks!
HOWEVER, I am struggling for days to figure out, how I can add another column to show the previous 30 days too.
If I add the date filter as you showed it in your explanation it will be applied to all the data in the table. So this should work with formulas using DATE_IN function, right?
But I am very confused how this function really works. I tried to understand your instructions here, but it makes no sense to me when I see the results.
Here an example, where I thought this would return the values of the last 30 days:
IF(DATE_IN(DATE(@A:A;,"yyyyMMdd"),day,-30),@B:B;,BLANK())
But this seems not to give values of the last 30 days? So I think I misunderstand the functionality of this formula.
If I then want to add another column with the previous 30 days, I think I should add a BETWEEN function that includes day,-60 and day,-30?
I really hope you can help me figure it out because once I understand, there are a lot of widgets I could finally build :D
Kind regards
Tom
Hi Thomas
Thanks for the question.
The following examples will help to explain the DATE_IN function.
If you use the unit month and the relative value of 0 with the DATE_IN function, the result will be the dates for the current month.
If you then change the relative value from 0 to -1, the result will be the dates for the previous month.
If you use the unit day and the relative value of 0 with the DATE_IN function, the result is the current day, ie 10 Feb 2021
If you use the unit day and the relative value of -1 with the DATE_IN function, the result is yesterday's date, ie 9 Feb 2021
and so on.
It looks like you want to build a Bar/Line chart that will display both the last 30 days and the previous 30 days.
So to do this using filtering you can proceed as follows.
For your x:axis, you would select the date column in your data source, and in the Properties panel, you need to check the input format is in the format of the date in your data source, and the output format is in the format that you want to see in your Klip. You can also select, group repeating labels here to get one instance for each date, if there are multiple instances of the same date. When you select group here, it will automatically apply a groupby to the series sub-components.
For the series, last 30 days, you will need to use the IF function because we want both true and false in order to align with the dates in our x:axis.
So your IF formula might be:
and then you adjust the -1 to -2 to get the month before that.
However, you might like to have the days on the x:axis, which requires you to group the dates and change the display format to day (d), and then have the two series map to those days.
One series might be displayed as a bar chart, and the other series might be displayed as a line chart in order to make each series easier to visualize.
Hope this helps, Yvonne
Thanks for your prompt response, Yvonne!
We are getting closer! I actually was that far already. However, using month -1 vs month -2 is not the same as -30day vs -prev30days, right?
And this results in LastMonth vs PrevLastMonth.
The ultimate goal here is either having the 30days from today into the past, or the current month vs the same days in prev month.
So 1-10th Feb vs 1-10 Jan for example.
I cannot see how this can be achieved with the formula you described to me, but maybe it is just me misunderstanding it?
Thanks!
Tom