Set Expression for Monthyear conditions

[Prasad1318]

Hi all,

 I have a problem in creating line chart.

I am having following fields

1. Date Created, closure Date, type

For eg if I need to calculate count of open type  based on conditions for month of june

1. type = open

2. date created <= monthend(june) - 30/06/2022of that particular month

3. Closure date > monthend() - 30/06/2022

The  conditions should get updated based on every month.

I tried building KPI and I achieved it with variable by using variable input.

How can i achieve it in the line chart.

can anybody please help me on this

Thanks in advance

#set expression 

[MendyS]

Hi

try something like  - 
 Count({<Type= {'Open'}, CreateDate={">=$ ('VarOfCreateDate')"},CloseDate={">=$ ('VarOfCloseDate')"}>} YourMeasure )

[Prasad1318]

Hi MendyS

Thank you for the reply 

I assume varofcreate data and Varofclose date is variables.

yes I tried it and created KPI and it worked. By using variable input I can change the month in the variables accordingly the count is coming correct. 

l need the same output for a line chart where monthyear is  dimension. 
if i used above expression as measure the output showing  as 0

Can you help how to write it in charts

thanks

[MendyS]

hi @Prasad1318 

you need to decide if you want to connect the field 'MonthYear'  to the date created  or the Closure date

[Prasad1318]

Yes MendyS

I connected monthyear with Date created using master calendar.

[SunilChauhan]

i have use this example/Sample

Load *,Num(DateCreated) as DateCreatedNum,num([Closuer Date]) as ClosureDateNum Inline [
ID, Type , DateCreated, Closuer Date
1, Open, 05/09/2022, 12/12/2022
6, Open, 05/09/2022, 12/12/2022
2, Open, 06/06/2022, 13/11/2022
3, Closed, 07/03/2022, 14/05/2022
4, Closed, 07/04/2022, 14/06/2022
5, Closed, 07/03/2022, 14/05/2022
];

DImension: DateCreated

Expression: 

Count({<Type={'Open'},DateCreatedNum={"<=$(vMonthEnd)"},ClosureDateNum={">=$(vMonthEnd)"}>}ID)

hope this helps

[Prasad1318]

Hi

Thanks for the reply

But what the vMonthEnd variable is having?

thanks

[SunilChauhan]

=Num(floor(MonthEnd(Today()))).

Are you not able to open Qvf attached?