Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Revenue of 1st day of each month

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
DateValue
1/1/20205
1/2/202050
1/3/202017
2/1/202015
2/2/202030
2/3/20202
3/1/20202
3/2/20202
3/3/20202

 

Output
Date(MM/DD/YYYY)Value
1/1/20205
2/1/202015
3/1/20202
Labels (3)
19 Replies
Highlighted
Creator III
Creator III

Hi @sankvag7 ,

Try below code, it solve your requirements: get the revenue of 1st day each month and also provide MoM comparison.

 

Source:
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,20
3/2/2020,3
3/3/2020,6
4/2/2020,25
4/3/2020,6
4/4/2020,3
5/1/2020,0
5/2/2020,0
5/3/2020,20
5/4/2020,10
];

// Get 1st day has revenue in month
_tmp:
Load 
	MonthName(Date) As Month, 
    Min(Date)  as Date
Resident Source
Where not IsNull(Value) and Value <> 0
Group by MonthName(Date);

Left Join
	Load Date, Value
Resident Source;

Drop Table Source;

// Calculate MoM
_result:
Load 
	Month, 
    MonthStart(Month) as MonthStart,
    Date as RevenueBeginDate, 
    Value,
    Peek(Value) as LM, 
    Value - Peek(Value) as MoM
Resident _tmp
Order by Date;

Drop Table _tmp;

 

 this is the result:

Quy_Nguyen_1-1598382964340.png

 

 

Highlighted
Contributor
Contributor

Thanks @Quy_Nguyen . That is helpful.

How can I get maxmonth(value),prevmonth(value) MoM change, MoM% change If I want to show it in a KPI scorecard?

Highlighted
Creator III
Creator III

Are you trying to get the latest month's value and compare to previous month?

For example in the data above, we will get  data of May and compare to April? If yes, you could try this:

Current latest month: Sum({<RevenueBeginDate = {"$(=Date(Max(RevenueBeginDate)))"}>} Value)

previous month: Sum({<RevenueBeginDate = {"$(=Date(Max(RevenueBeginDate)))"}>} LM)

for the % change you just have to do the division

Highlighted
MVP
MVP

How you are going to represent MoM ? In KPI?

Highlighted
Contributor
Contributor

Hi @Quy_Nguyen , my old dimensions and values don't went away after I used the script you mentioned. Do I have to use load *,  in any of the temp tables?

Highlighted
Contributor
Contributor

@Kush 

I used the script you mentioned and get the values on a monthly level.

Now I want to be able to also show the following in a KPI:

1.  Value for  Current month start date, if March is the current month then the value on 3/1/2020 should be shown

Sum({<date = {"$(=Date(Max(date)))"}>} Value)  

2.  Value for prev month start date - which would be the value for Feb on 2/1/2020 if current month is March

Sum({<date = {"$(=Date(Max(date)-1))"}>} Value)

3. MoM would be the change from (Current month start date - Prev month start date ) which would be 

3/1/2020 - 2/1/2020

Sum({<date = {"$(=Date(Max(date)))"}>} Value) -

Sum({<date = {"$(=Date(Max(date)-1))"}>} Value)

Highlighted
MVP
MVP

Assuming your date format is proper then you can try below

// Current Month start

=sum({<Date={"$(=date(max({<First_Date_Flag={1}>}Date)))"}>}Value)

// Previous Month start

=sum({<Date={"$(=date(max({<First_Date_Flag={1}>}Date,2)))"}>}Value)

// MoM

sum({<Date={"$(=date(max({<First_Date_Flag={1}>}Date)))"}>}Value)-

sum({<Date={"$(=date(max({<First_Date_Flag={1}>}Date,2)))"}>}Value)

Highlighted
Contributor
Contributor

@Kush 

I'm getting total of all the months including null dates for the Current month KPI. 

Do you know what could be causing that?

Highlighted
Creator III
Creator III

Just contribute 1 more way, it's aggregated dimension. with your raw data, insert a table:

- Dimension: aggr(Date(min(vDate),'M-D-YYYY'), vMonth, vYear)

you might need to pre-calculate vMonth, vYear from data load: Load date#(Date, 'M/D/YYYY') as vDate, num(Month(Date),'0') as vMonth, year(Date) as vYear resident data.

- Measure: FirstSortedValue(Value,vDate)

LOL, everytime I saw your topic, I figured out another way

Highlighted
MVP
MVP

It will work in KPI object ..where you are using this expression?