Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have 2 data sets, one contains only Open Cases and one contains all Closed cases. Week-Ending is the common key between these 2 data sets. Both datasets are joined in Qlik via Week_Ending
I have a simple table chart where I added the following expression to get the total and average of Open and closed cases as shown in the screenshot-
Avg(Aggr(Count(CASE_ID), [export-1.WEEK_ENDING-WEEK_ENDING]))
and for Open - Closes cases -
Avg(Aggr(Count([CASE_ID])-COUNT([export-1.CASE_ID]), [export-1.WEEK_ENDING-WEEK_ENDING]))
Above expression gives me the total and overall average of all open and closed cases and the difference between them.
But my requirement is-
I am looking help to fulfill above 2 requirements.
Thank you all in advance for your help.
an if statement might help..
1. excuding current week:
if( weekend(today())<>[export-1.WEEK_ENDING-WEEK_ENDING],Avg(Aggr(Count(CASE_ID), [export-1.WEEK_ENDING-WEEK_ENDING])))
2. Excluding where avg is <5:
if((Avg(Aggr(Count([CASE_ID])-COUNT([export-1.CASE_ID]), [export-1.WEEK_ENDING-WEEK_ENDING])))>5,Avg(Aggr(Count([CASE_ID])-COUNT([export-1.CASE_ID]), [export-1.WEEK_ENDING-WEEK_ENDING])))
I am also learning, would be great to know if this works.
Hi, thank you for the reply.
I tried using the above expressions but on first one, since I don't have master calendar defined, weekend(today) expression is not working. I generated the data including weekend via SQL query.
In the second expression, even though it does not show any error in the expression but no result appear. Its just "__"
weekend(today()) does not need master calendar.. I tried and got this for today.
Hi,
Thank you for looking in to it. I tried using the same expression as you suggested in the column listed "Test"-
if( weekend(today())<>[export-1.WEEK_ENDING-WEEK_ENDING],Avg(Aggr(Count(CASE_ID), [export1.WEEK_ENDING-WEEK_ENDING])))
but I am getting same result as I am getting using following expression listed under column "All Open Claims"-
Avg(Aggr(Count(CASE_ID), [export-1.WEEK_ENDING-WEEK_ENDING]))
Result is not excluding Weekending 7/23/2022