topic Re: Expression Edits in QlikView

Expression Edits

NavinReddy — Fri, 15 Nov 2024 23:46:55 GMT

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,

Re: Expression Edits

Taoufiq_Zarra — Mon, 19 Oct 2020 10:29:58 GMT

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

Re: Expression Edits

Taoufiq_Zarra — Mon, 19 Oct 2020 10:31:09 GMT

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

Re: Expression Edits

NavinReddy — Mon, 19 Oct 2020 10:47:22 GMT

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,

Re: Expression Edits

NavinReddy — Mon, 19 Oct 2020 10:49:54 GMT

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

Re: Expression Edits

Taoufiq_Zarra — Mon, 19 Oct 2020 11:29:59 GMT

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

Re: Expression Edits

NavinReddy — Mon, 19 Oct 2020 15:12:41 GMT

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,

Re: Expression Edits

Taoufiq_Zarra — Mon, 19 Oct 2020 15:14:55 GMT

@NavinReddy if you have to select Month then use

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

or you can share the expected you want ?

Re: Expression Edits

NavinReddy — Mon, 19 Oct 2020 16:25:17 GMT

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,

Re: Expression Edits

MK_QSL — Mon, 19 Oct 2020 16:48:47 GMT

use this...

Re: Expression Edits

NavinReddy — Mon, 19 Oct 2020 17:05:57 GMT

Thanks a lot @MK_QSL and @Taoufiq_Zarra its working fine