Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I really need your help with a graphic rolling month and indirect set analysis.
The result should show the count per month for calls, but at the end of September we changed the calculation for counting calls.
That’s why I have to link this two calculations
=
count({$<TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr,-2),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr,-7),'MMM-YYYY')))"},
TK_Monat,TK_Status={'Alerting','Pickup'}>}
Distinct callid)
+
((count({$<TK_MonatJahr= {"$(='<='&Only(TK_MonatJahr)&'>'&Only(date(addmonths(TK_MonatJahr,-2),'MMM-YYYY')))"},TK_Monat,id = p({<TK_prevStatus = {'WelcomeAnnouncement'}>}) * p({<TK_Status = {'*'} - {'Announcement','Lost'}>}) >}
Distinct callid)))
But for my second calculation it doesn’t show the rolling month, just the selected month. I think the problem is the indirect set analysis.
I suspect the answer is obvious, but I cannot find it!
Thanks,for any help
I really hope, anybody can help.
Thanks and best regards
Sabine
Hi!
Can you upload a sample qvw?
Thanks,
Elena
Hi Elena,
please find attached a sample qvw.
Best regards
Sabine
I saw your qvw: can you try with this in the second part of sum?
(count({$<TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr, -0),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr, -2),'MMM-YYYY')))"}
,id = p({<TK_Monat, TK_prevStatus = {'WelcomeAnnouncement'}>}) * p({<TK_Monat, TK_Status = {'*'} - {'Announcement','Lost'}>}), TK_Monat
>}
Distinct callid))
Hi Elena,
thanks for your help, it seems to work. I really had spent of time for searching a solution.
But I have another question, cause my expression is a bit more complicated. I also need to add calls, which have multiple conditions like this.
Every calls has only 1 callid, but several ids for each state.
I need all Calls, which have:
state (TK_Status) = 'Announcement' and
previousstate (TK_prevStatus) ='WelcomeAnnouncement'
if this first condition is complied, the next expression should show
2. id with
previousstate = 'Announcement' and
state = 'Lost' and
Duration (TK_Dauer) >=20
Plus Id with
previoustate = {'Announcement'} and
state = {'*'} - {'Lost'}
This is my expression, but I just get the value for the filtered month:
(sum
({<TK_Monat,TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr, -0),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr, -2),'MMM-YYYY')))"}>}
aggr(
if(
aggr
(count({$<TK_Monat,TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr, -0),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr, -2),'MMM-YYYY')))"},
id = p({<TK_Monat,TK_prevStatus = {'WelcomeAnnouncement'}>}) * p({<TK_Monat,TK_Status = {'Announcement'}>})>}
Distinct callid),callid)=1,
count({<TK_Monat,TK_MonatJahr= {"$(='<='&Only(date(addmonths(TK_MonatJahr, -0),'MMM-YYYY'))&'>'&Only(date(addmonths(TK_MonatJahr, -2),'MMM-YYYY')))"},
id = p({<TK_Monat,TK_prevStatus = {'Announcement'}>}) * p({<TK_Monat,TK_Status = {'Lost'}>})* p({<TK_Monat,TK_Dauer = {">=20"}>})>
+
<id = p({<TK_Monat,TK_prevStatus = {'Announcement'}>}) * p({<TK_Monat,TK_Status = {'*'} - {'Lost'}>}) >}
Distinct callid)),callid)))
I update my qvw with a table with this expression.
Thanks for your help in advance and best regards
Sabine