Pivot table expression based on previous and current week data.

[#1]

Hello,

I am trying to find the best and easiest method to achieve cross week analysis in pivot.

Lets say we have below data:

Date	Project	Stage	Cost
7/12/2016	A	Closed	100
30/11/2016	A	In progress	100
7/12/2016	B	In progress	200
30/11/2016	B	In progress	200

I want to have blow output in pivot table:

Date	Amount In progress	Closed amount this week
7/12/2016	200	100
30/11/2016	300	-

So there is one dimension:

- Date

Two expressions:

- "Amount in progress" - this I am able to easily calculate

- "Closed amount this week" - is a real struggle.

This should show how much of Cost got Closed this week.

So basically I must check if project was "in progress" last week and is closed this week.

If yes, I must show the closed amount.

PS. I can't simply subtract current in progress amount from last weeks, because this is one of many scenarios which i must code

Thank you for the help!

[nagarjuna_kotha]

as per ur output No need to do any additional; check attached file:

[nagarjuna_kotha]

[lironbaram]

from

your table it seems that

sum({<Stage={'Closed'}>}Cost)

should work for you

[adamdavi3s]

Ok but based on your data, you have what was closed this week?

Or on 14/12/2016 will project A still show up as closed?

I've made an assumption that this is the case on your data (hence the complication) and this particular scenario is a good candidate for using the 'previous' function in the load script to make a nice simple flag.

I've attached a QVW but in case you only have personal edition see my working below:

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

Script:

testdata:


LOAD * INLINE [
Date,Project,Stage,Cost
14/12/2016,A,Closed,100
14/12/2016,B,Closed,200
07/12/2016,A,Closed,100
30/11/2016,A,In progress,100
07/12/2016,B,In progress,200
30/11/2016,B,In progress,200
];
NoConcatenate
finaldata:
LOAD *, if(Project = Previous(Project) and Stage='Closed' and Previous(Stage)='In progress',1,0) as ClosedFlag
resident testdata
order by Project,Date asc;
drop table testdata;

Output:

Formula:

=sum({<Stage={'In progress'}>}Cost)

=sum(if(ClosedFlag=1,Cost))

=sum({<Stage={'Closed'}>}Cost)

[#6]

Hey,

Thanks for your answers, I think my example is not good enough.

Allow me to state this: I need to precisely base my logic on below criteria:

- sum only if project in previous week was in In progress stage and this week is in Closed stage.

Lets add two more entries: 

Date	Project	Stage	Cost
7/12/2016	A	Closed	100
30/11/2016	A	In progress	100
7/12/2016	B	In progress	200
30/11/2016	B	In progress	200
7/12/2016	C	Closed	300
30/11/2016	C	Closed	300

Now your expression will return 400 instead of 100 which would be correct as per my logic.

As I mentioned - there are many more stage combinations which I will need to code on separate expressions.

I am looking for some clever way to do it.

Thanks!

[adamdavi3s · Accepted solution]

I think my solution will work for you then?

I mean if you want to get clever in the front end rather in the  script (I always prefer the script) then the above / below before / after formula are probably what you want to work with

Missing Manual - Before() and After()

[#8]

Hi,

Your assumption is correct.  This is basically how I wanted to do this, but was thinking it is not the best way to start with it.

I am still thinking how this can be done using set analysis (and if it can be done using it)

Anyways, this helps. Thanks!

[#9]

Hi Adam,

I think I am trying to over-complicate it to myself for no reason

I will try with your solution.

Thanks!

[adamdavi3s]

I am exactly the same don't worry, my mind always goes to set analysis first but often we can make a much simpler and more responsive app with a little script trickery