Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
NavinReddy
Creator II
Creator II

Expression Edits

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

Labels (3)
2 Solutions

Accepted Solutions
Taoufiq_Zarra

@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 :

Capture.PNG

 

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
];

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

MK_QSL
MVP
MVP

10 Replies
Taoufiq_Zarra

@NavinReddy  can you share a sample data from the Metric_Month field ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Taoufiq_Zarra

you must verifie Metric_Month format, if it's monthname format ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
NavinReddy
Creator II
Creator II
Author

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

NavinReddy
Creator II
Creator II
Author

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

Taoufiq_Zarra

@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 :

Capture.PNG

 

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
];

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
NavinReddy
Creator II
Creator II
Author

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

Taoufiq_Zarra

@NavinReddy  if you have to select Month then use

=Count({<Date = {">=$(vMaxDate2) <=$(vMaxDate)"}>} value_id)

or you can share the expected you want ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
NavinReddy
Creator II
Creator II
Author

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

MK_QSL
MVP
MVP

use this...