Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

rolling month and indirect set analysis

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

4 Replies
Anonymous
Not applicable
Author

Hi!

Can you upload a sample qvw?

Thanks,

Elena

Not applicable
Author

Hi Elena,

please find attached a sample qvw.

Best regards

Sabine

Anonymous
Not applicable
Author

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))

Not applicable
Author

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:

  1. id with

          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