cross tab requirement

[#1]

Hi

I have a cross tab requirement to get the #projects based on the status of project by Month. This should always show the latest available rolling window of 12 months and the report should not be variant to the selections of Month,year,quarter. I did came up with logic in load script to flag each project based on the status and a flag to identify the latest available 12 months and a computed dimension for the 4 metrics that you see on Y- axis in the below screenshot using valuelist function.

Ive used the following expression to calculate the metrics.

Here are the problems that Im facing...

-->Report is changing if I make selections in year/quarter/month list boxes

-->if we don't have data to a specific selection report isn't showing any data (im expecting 0  or blank or -)

thanks

Chandra

Expression:

(ValueList('Total Number Of Projects', 'Total Number Of Discretionary Projects',  'Total Number Of KTLO Projects', 'Total Number Of Completed/Cancelled Projects') = 'Total Number Of Projects',
count({$<[PCD Fiscal Year]=,[PCD Fiscal Quarter]=,[PCD Month Abbr]=,[PCD Last12MonthsFlag]={1}>} [PCD Project]),
if(ValueList('Total Number Of Projects', 'Total Number Of Discretionary Projects',  'Total Number Of KTLO Projects', 'Total Number Of Completed/Cancelled Projects') = 'Total Number Of Discretionary Projects',
sum({$<[PCD Fiscal Year]=,[PCD Fiscal Quarter]=,[PCD Month Abbr]=,[PCD Last12MonthsFlag]={1}>} [PCD-CP Discretionary Project Flag]),
if(ValueList('Total Number Of Projects', 'Total Number Of Discretionary Projects',  'Total Number Of KTLO Projects', 'Total Number Of Completed/Cancelled Projects') = 'Total Number Of KTLO Projects',
sum({$<[PCD Fiscal Year]=,[PCD Fiscal Quarter]=,[PCD Month Abbr]=,[PCD Last12MonthsFlag]={1}>} [PCD-CP KTLO Project Flag]),
if(ValueList('Total Number Of Projects', 'Total Number Of Discretionary Projects',  'Total Number Of KTLO Projects', 'Total Number Of Completed/Cancelled Projects') = 'Total Number Of Completed/Cancelled Projects',
sum({$<[PCD Fiscal Year]=,[PCD Fiscal Quarter]=,[PCD Month Abbr]=,[PCD Last12MonthsFlag]={1}>} [PCD-CP Completed/cancelled/held Project Flag]),
))))

Sample file Header:
[PCD Month Abbr],[PCD Fiscal Year],[PCD Fiscal Quarter],[PCD Project],status,leader,projectManager,[PCD-CP Discretionary Project Flag],[PCD-CP KTLO Project Flag],[PCD-CP Completed/cancelled/held Project Flag],[PCD Last12MonthsFlag]

Jan,2013,Q2,ProjE,Inprogress,Person1,PersonX,1,0,0,0

Feb,2013,Q2,ProjE,Completed,Person1,PersonX,0,0,1,0

Jan,2014,Q2,ProjA,Inprogress,Person1,PersonX,0,1,0,1

Feb,2014,Q2,ProjA,Inprogress,Person1,PersonX,0,1,0,1

Mar,2014,Q2,ProjA,Complete,Person1,PersonX,0,0,1,1

Jan,2014,Q2,ProjB,Inprogress,Person2,PersonY,0,1,0,1

Feb,2014,Q2,ProjB,Complete,Person2,PersonY,0,0,1,1

Feb,2014,Q2,ProjC,Started,Person3,PersonX,1,0,0,1

We should show 2013 and 2014 in the listbox for year... but in report we should not show JAN 2013 and FEB 2013 because those arent in the last 12 months. And report should reflect the changess based on the selections on Leader,ProjectManager listboxes. If I select Person3 in IT Leader List box, we should show values as 0/null in Jan and Mar months because we have data only in Feb.

Report layout:

[SergeyMak]

Please share your app or scrambled sample

[#3]

Thanks Sergey. I've updated original post with the sample file.

[SergeyMak]

I didn't find an app, only data. But as I understood you need help with app. Could you share your app? because I could reproduce everything, but it really takes more time, than just have a look on your app and give an advice and/or a solution.

[#5]

share your document so i can help your

[#6]

Thank you Ahsan & Sergey.

This is my first post and so my apologies if Im not clear. I've uploaded .qvw file. Hope this helps. Please let me know.

thanks

Chandra

[SergeyMak]

Hi,

I used straight table with horizontal orientation and same appearance. But now if the selections of year and month don't affect your table

PFA

!

[#8]

Thank you Sergey.  Can you please explain what I did wrong. I am asking cause want to learn from my mistake. By the way... Is there a way to show metric values as 0 for the months where we do not have data (ex: if we select Person3- report isn't showing Jan and Mar. I want to display 0 if we don't have data. )  thanks Chandra

[SergeyMak · Accepted solution]

You can see in my solution that I used straight table (not pivot) and created 4 expression (instead one) to calculate your metrics. But you tried to use synthetic dimension. Probably it was a cause of problem.

To show all month for Person3, try to select Show all values for PCD Month Abbr on dimension tab and deselect Suppress zero values on Presentation tab.

[#10]

Hi Sergey,

It worked but I had to select both Show all values and Supress Null values on PCD Month ABr dimension and deselected suppress zero values on metrics presentation tab.

thanks a bunch.... I will change my approach and will follow your instructions.

thanks

Chandra