Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi
try something like -
Count({<Type= {'Open'}, CreateDate={">=$ ('VarOfCreateDate')"},CloseDate={">=$ ('VarOfCloseDate')"}>} YourMeasure )
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
hi @Prasad1318
you need to decide if you want to connect the field 'MonthYear' to the date created or the Closure date
Yes MendyS
I connected monthyear with Date created using master calendar.
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
Hi
Thanks for the reply
But what the vMonthEnd variable is having?
thanks
=Num(floor(MonthEnd(Today()))).
Are you not able to open Qvf attached?