topic Re: Set Expression for Monthyear conditions in App Development

Set Expression for Monthyear conditions

Prasad1318 — Mon, 05 Sep 2022 07:54:41 GMT

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

Re: Set Expression for Monthyear conditions

MendyS — Mon, 05 Sep 2022 08:11:35 GMT

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

Re: Set Expression for Monthyear conditions

Prasad1318 — Mon, 05 Sep 2022 09:37:23 GMT

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

Re: Set Expression for Monthyear conditions

MendyS — Mon, 05 Sep 2022 10:35:30 GMT

hi @Prasad1318

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

Re: Set Expression for Monthyear conditions

Prasad1318 — Mon, 05 Sep 2022 11:10:52 GMT

Yes MendyS

I connected monthyear with Date created using master calendar.

Re: Set Expression for Monthyear conditions

SunilChauhan — Mon, 05 Sep 2022 12:47:02 GMT

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

Re: Set Expression for Monthyear conditions

Prasad1318 — Tue, 06 Sep 2022 05:42:31 GMT

Thanks for the reply

But what the vMonthEnd variable is having?

thanks

Re: Set Expression for Monthyear conditions

SunilChauhan — Tue, 06 Sep 2022 05:47:16 GMT

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

Are you not able to open Qvf attached?