checking if there are Values in previousWeek in Dimension

[#1]

hello,

i have some trouble to find the right solution.

let`s say i have following table:

id	week	flag
1	1	1
1	3	1
1	4	1
2	1	1
2	2	1
2	3	1

what i´m trying to achieve is following result:

Dimension: week

expressions:

- count all ids which have an entry in previous week and this week --> active previous week + active this week

- count all ids which have no entry in previous week but entry in this week --> active previous week + not active this week

- count all ids which have an entry in previous week but no entry in this week --> not active previous week + active this week

Week	active previous week + active this week	active previous week + not active this week	not active previous week + active this week
1	-	-	-
2	1	1
3	1		1
4	1	1

I struggeling around for a while an couldn`t find any solution. I tried it with aggr function and above function but have no satisfying results.

I hope somebody can help me! it´s really important

Thanks

Stevve

[sinanozdemir]

Hi Steve,

Question:

in your second table, you are showing in column "active previous week + active this week" for week 2 as 1 so I am a bit confused with this logic. Is week 4 the current week? If so, how is it possible to have 1 for week 2 in active previous week + active this week?

Thanks

[#3]

hi sinanozdemir,

"this week" means that is is calculated by week related to dimension. so for week2 only id2 was active (which you can see in table1. in week1 there are existing entries for id1 and for id2.

--> expression "active previous week + active this week" for week2:  id1 has no entry in week2, so it will not be counted, id2 has an entry in week2 (active this week) AND an entry in week1 (active in previous week).

so id2 shell be counted -> result = 1

hope that helps

[sinanozdemir]

Hi Steve,

Sorry for the delay. I just forgot about this and I remembered when I had to do something similar to your question. Here is my approach and I am hoping it helps:

I handled everything in the back-end:

And in a straight table, I first pulled week as the first dimension and then I typed below scripts for each column:

For the first column "active previous week + active this week":

For the second column "active previous week + not active this week":

For the third column "not active previous week + active this week":

After that the straight table should look like the below one:

I am also attaching the qvw and I know this approach seems messy, but I personally think that the order of fields does matter in order to come up with the required measures.

Hope this helps.

Thanks