Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
i want to display 3 months values trend using below expression and variables, i tried with below expression im getting "0" value, Any expression Suggestions.
variables:
MonthName(Date) as Metric_Month
vMaxDate=monthname(addmonths(max(Date),0))
vMaxDate2=monthname(addmonths(max(Date),-2))
Expression:
=Count(DISTINCT{<Month=,Metric_Month={">=$(=vMaxDate)<=$(=vMaxDate2)"}>}value_id)
Thanks & Regards,
NR
@NavinReddy why not
vMaxDate=Date((addmonths(max(Date),0)),'D/M/YYYY')
vMaxDate2=Date(addmonths(max(Date),-2),'D/M/YYYY')
then
=Count({<Month=,Date = {">=$(vMaxDate2) <=$(vMaxDate)"}>} value_id)
for example this sample :
the input :
Data:
LOAD *,MonthName(Date#(Date,'M/D/YYYY')) as Metric_Month,Month(Date#(Date,'M/D/YYYY')) as Month INLINE [
Date,value_id
10/1/2020,1
10/4/2020,2
10/5/2020,3
10/6/2020,4
7/1/2020,5
8/1/2020,6
9/2/2020,7
9/7/2020,8
9/8/2020,9
10/13/2020,10
10/14/2020,11
10/16/2020,12
];
use this...
@NavinReddy can you share a sample data from the Metric_Month field ?
you must verifie Metric_Month format, if it's monthname format ?
Hi @Taoufiq_Zarra ,
Thanks for your reply,
Below is the Date filed I am converting into monthname format(Metric_Month),
It is date field. Below i have added the date filed data for reference.
MonthName(Date) as Metric_Month
Date
10/1/2020
10/4/2020
10/5/2020
10/6/2020
7/1/2020
8/1/2020
9/2/2020
9/7/2020
9/8/2020
10/13/2020
10/14/2020
10/16/2020
Thanks & Regards,
NR
Hi @Taoufiq_Zarra ,
Metric_Month is date field, im converting into monthname format.
MonthName(Date) as Metric_Month
Date
10/1/2020
10/4/2020
10/5/2020
10/6/2020
7/1/2020
8/1/2020
9/2/2020
9/7/2020
9/8/2020
10/13/2020
10/14/2020
10/16/2020
@NavinReddy why not
vMaxDate=Date((addmonths(max(Date),0)),'D/M/YYYY')
vMaxDate2=Date(addmonths(max(Date),-2),'D/M/YYYY')
then
=Count({<Month=,Date = {">=$(vMaxDate2) <=$(vMaxDate)"}>} value_id)
for example this sample :
the input :
Data:
LOAD *,MonthName(Date#(Date,'M/D/YYYY')) as Metric_Month,Month(Date#(Date,'M/D/YYYY')) as Month INLINE [
Date,value_id
10/1/2020,1
10/4/2020,2
10/5/2020,3
10/6/2020,4
7/1/2020,5
8/1/2020,6
9/2/2020,7
9/7/2020,8
9/8/2020,9
10/13/2020,10
10/14/2020,11
10/16/2020,12
];
Hi @Taoufiq_Zarra , thanks a lot for your reply,
i have tried with expression to create 3 months trend, based on month selection. its not working for me,
kindly help me,
Thanks& Regards,
NR
@NavinReddy if you have to select Month then use
=Count({<Date = {">=$(vMaxDate2) <=$(vMaxDate)"}>} value_id)
or you can share the expected you want ?
Hi @Taoufiq_Zarra , i have used the expression, no luck , below is the expected output. if i select current month Oct'20 , i have to display 3 months trend. i have attached the file. kindly help me.
Month Oct-20 Sep-20 Aug-20
Value 43 24 6
Thanks & Regards,
NR
use this...