topic Re: rolling month and indirect set analysis in QlikView

rolling month and indirect set analysis

Thu, 08 Dec 2016 10:52:44 GMT

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

Re: rolling month and indirect set analysis

Anonymous — Mon, 12 Dec 2016 10:49:38 GMT

Hi!

Can you upload a sample qvw?

Thanks,

Elena

Re: rolling month and indirect set analysis

Mon, 12 Dec 2016 15:08:15 GMT

Hi Elena,

please find attached a sample qvw.

Best regards

Sabine

Re: rolling month and indirect set analysis

Anonymous — Mon, 12 Dec 2016 15:43:08 GMT

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

Re: rolling month and indirect set analysis

Tue, 13 Dec 2016 11:14:04 GMT

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:

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