1 2 3 31 Replies Latest reply on Aug 3, 2018 4:45 AM by Mahfooj Khan Go to original post
• ###### 15. Re: Calculating Average sales for preceding months

Hi Koushik,

Your parameter should be of date type. Make sure your date field and parameter are of same type. If it's not then convert it to date.

~Tushar

• ###### 16. Re: Calculating Average sales for preceding months

Both my fields are in string. I am not getting any error but getting NULL values

• ###### 17. Re: Calculating Average sales for preceding months

As said in the previous reply, change the data type to date.

• ###### 18. Re: Calculating Average sales for preceding months

Hi Koushik,

Find my approach below,

Create a Date parameter like this

then create two calculated fields

Avg. Sales:

{AVG(IF YEAR([Month Date])=YEAR([Select Month]) AND

DATEDIFF('month',DATETRUNC('month',[Month Date]),[Select Month])>0

THEN [Sales] END)}

Current Sale:

IF DATETRUNC('month',[Month Date])=[Select Month] THEN [Sales] END

once done then drag the fields in canvas and check

Let us know if this help.

Mahfooj

2 of 2 people found this helpful
• ###### 19. Re: Calculating Average sales for preceding months

Hello Mahfooj, I am getting below error for Datetrunc and year.

I am using Month_parameter as a string and month field coming from database is a string as well. I cannot change the datatype now as it effects entire dashboard

Regards,

Koushik.

• ###### 20. Re: Calculating Average sales for preceding months

hi koushik,

Please use ZN(),check the below

BR,

Bharat

• ###### 21. Re: Calculating Average sales for preceding months

Hello, Can you modify the below logic when month & month_parameter are strings.

{AVG(IF YEAR([Month Date])=YEAR([Select Month]) AND

DATEDIFF('month',DATETRUNC('month',[Month Date]),[Select Month])>0

THEN [Sales] END)}

Regards,

Koushik.

• ###### 22. Re: Calculating Average sales for preceding months

Hmm..

find the revised calculations

Avg. Sales:

{AVG(IF YEAR([Month Date])=YEAR(DATEPARSE('MMM-YY',[Select Month])) AND

DATEDIFF('month',DATETRUNC('month',[Month Date]),DATEPARSE('MMM-YY',[Select Month]))>0

THEN [Sales] END)}

Current Sale:

IF DATETRUNC('month',[Month Date])=DATEPARSE('MMM-yy',[Select Month]) THEN [Sales] END

Let us know if this help.

Mahfooj

• ###### 23. Re: Calculating Average sales for preceding months

Hi, Koushik

thanks for the sample data.

Please find my solution attached.

Below is steps for your reference.

Hope this helps

ZZ

• ###### 24. Re: Calculating Average sales for preceding months

Same error

• ###### 25. Re: Calculating Average sales for preceding months

Hello ZZ, the problem is, Parameter and month fields datatype is string and if i change the datatype, my entire dashboard gets disturbed.

Regards,

Koushik.

• ###### 26. Re: Calculating Average sales for preceding months

Logic:

{AVG(IF YEAR([month_])=YEAR(DATEPARSE('MMM-YY',[month_])) AND

DATEDIFF('month',DATETRUNC('month',[month_]),

DATEPARSE('MMM-YY',[month_ Parameter]))>0

THEN XXXX END)}

Error:

• ###### 27. Re: Calculating Average sales for preceding months

Hi, Koushik

the idea is to convert the string to date type and then use the date type fields in the calculation

Hope this helps

ZZ

• ###### 28. Re: Calculating Average sales for preceding months

Brother you should mention the data type of your [Month Date] fields, which is string

What happens when we export the data from any db in excel, using clipboard/export then excel convert the data type of each field automatically. Same thing happened here If you open your attached excel, excel recognizing [Month Date] field as date instead of string. See the screenshot

That's why solution didn't work for you,

No worries, we need to first convert [Month Date] as date

call it

Month Year:

DATE(DATEPARSE('yy-MMM',[Month Date]))

Now replace [Month Date] with above [Month Year] calculated field in both the calculated field which I have shared in my previous reply.

Avg. Sales

{AVG(IF YEAR([Month Year])=YEAR(DATEPARSE('MMM-YY',[Select Month])) AND

DATEDIFF('month',DATETRUNC('month',[Month Year]),DATEPARSE('MMM-YY',[Select Month]))>0

THEN [Sales] END)}

Current Sale:

IF DATETRUNC('month',[Month Year])=DATEPARSE('MMM-yy',[Select Month]) THEN [Sales] END

Let us know if this work.

Mahfooj

• ###### 29. Re: Calculating Average sales for preceding months

Hello Mahfooj, Yes above logic is working perfectly without any errors, But I am getting everything as zero

Atleast i am not getting any error

Month Year:

DATE(DATEPARSE('Mmm-yy',[Month])) as my Month field  is in Jan-18 format.

Avg. Sales

{AVG(IF YEAR([Month Year])=YEAR(DATEPARSE('Mmm-yy',[Month Parameter])) AND

DATEDIFF('month',DATETRUNC('month',[Month Year]),DATEPARSE('Mmm-yy',[Month_Parameter]))>0

THEN [Sales] END)}

@ZZ:   I implemented your logic as well getting null/zero values. Here month_year is the dateparse of month and month_year_parameter is the date parse of month parameter.

{Fixed DATETRUNC('month',[Month_Year]):

sum( IF  YEAR([Month_Year])=YEAR([Month_Year_Param])

AND [Month_Year]< DATETRUNC('month',[Month_Year_Param])

THEN Sales

END

)

}

regards,

Koushik.

1 2 3