Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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()
as per ur output No need to do any additional; check attached file:
from
your table it seems that
sum({<Stage={'Closed'}>}Cost)
should work for you
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)
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!
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()
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!
Hi Adam,
I think I am trying to over-complicate it to myself for no reason
I will try with your solution.
Thanks!
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