Revenue of 1st day of each month

[sankvag7]

Hi Folks,

I want to calculate the value for 1st day of each month. I have tried various monthstart calculations in the script but having a hard time to get it. I'm posting the raw data and the output I will need.

Raw data
Date	Value
1/1/2020	5
1/2/2020	50
1/3/2020	17
2/1/2020	15
2/2/2020	30
2/3/2020	2
3/1/2020	2
3/2/2020	2
3/3/2020	2

Output
Date(MM/DD/YYYY)	Value
1/1/2020	5
2/1/2020	15
3/1/2020	2

[thi_pham]

I think there are a lot of way to achieve your expected output, I suggest a solution here:

you can get 1st date of a month by: MakeDate(year(Date), month(Date), 1)

Then, to make a table chart based on your raw data, you can do this:

dimension: =if (Date = MakeDate(year(Date), month(Date), 1), Date, null()) -> uncheck: include null value

measure: sum(value).

* If you want to handle it in data load: you might do the check add, then add a new column which is a kind of flag to recognize it's the first day of month. Then on chart, just count the one have flag is true.

[sankvag7]

@thi_pham  I'm getting blank and null values when I tried the first solution. I also unchecked the null values.

Previously I had tried sum({<DateNum={$(=max(DateNum))}>} value) and for today and then sum({<DateNum={$(=max(DateNum)-30)}>} value) for prev month. From these two I could get today and today-30 which let's me compare 2 dates from 2 months but I need the value of 1st date of the current month and value of the 1st date of prev month and so on...

[thi_pham]

It maybe due to your date format. 

Below is another way to get result from data load.

data:
Load * inline [
Date,Value
1/1/2020,5
1/2/2020,50
1/3/2020,17
2/1/2020,15
2/2/2020,30
2/3/2020,2
3/1/2020,2
3/2/2020,2
3/3/2020,2
];

[data_converted_date]:
NoConcatenate
Load date#(Date, 'M/D/YYYY') as Date,
Value,
if (day(Date) = 1, 1, 0) as IsFirstDayOfMonth
Resident data;

[Value_1st_day]:
NoConcatenate
Load Date, Value Resident data_converted_date
where IsFirstDayOfMonth = 1;
drop tables data, data_converted_date;

[sankvag7]

Hi @thi_pham , you were correct. My date format is MM/DD/YY that's the reason it was giving me a null value. It now gives me what I need when I used the first solution you had suggested. 

But there is another problem, for example if the first day of the month is 0 or null then it should be able to grab data from 2nd day and if 2nd day is also 0 or null then it should grab data from the 3rd day.

Is that possible?

[Kushal_Chawda]

try below

Data:
load Date,
Value,
monthname(Date) as Month
FROM Table;

left join(Data)
load Month,
date(min(Date)) as Date,
1 as First_Date_Flag
resident Data
Group by Month;

Now you can use below expression
=sum({<First_Date_Flag={1}>}Value)

[Kushal_Chawda]

corrected code

[sankvag7]

Thanks @Kushal_Chawda . Let me try that out and get back.

Also, how can I get MoM change, for example, date:02/01/2020 and Value:15 and date:03/01/2020 and value:40, then MoM should give me 25.

[sankvag7]

@Kushal_Chawda 

Can I use the below for MoM change?

MakeDate(max(year(date)), max(month(date)), 1)
-
MakeDate(max(year(date),-1), max(month(date),-1), 1)

[sankvag7]

Hi @Kushal_Chawda , that worked pretty well. Could you please let me know how can I get max(value) and max(value)-1 using the same flag First_Date_Flag={1}?

I want to do MoM change