Include Missing Data in Table

[ciaran_mcgowan]

Hi all,

I have an application where annual sales data is displayed in a simple straight table that ranks sales people. Now, my manager wants to see each of the sales figures for each week if they select it. So the annual data looks like this:

Rank	Employee	Total Sales
1	John	1,456,895
2	Paul	1,223,564
3	George	987,123
4	Ringo	402,564

I've edited the script to calculate the accumulative total by week and set that to display if a week/weeks are selected. The only problem is that if an employee doesn't register any sales for a week, they are omitted from the table, and the Rank becomes invalid/incorrect. Is there any way to include them in the table when there is no data? Also, there could be 4/5 week stretches where sales people might not register a sale.

Here is an example of what I'm seeing now. If I select week 24, all users have sales data:

Rank	Employee	Total Sales
1	John	600,234
2	Paul	534,276
3	George	307,659
4	Ringo	125,967

But if I select week 25, not all users have sales data and the table looks like this:

Rank	Employee	Total Sales
1	John	642,896
2	Ringo	142,215

But what I need to see is:

Rank	Employee	Total Sales
1	John	642,896
2	Paul	534,276
3	George	307,659
4	Ringo	142,215

Can this be done?

[sunny_talwar]

What are the expression that you have used here?

[ciaran_mcgowan]

Hi Sunny,

I used to use:

Sum({<Month=, Date=>} Sales)

But after creating the weekly accumulation in the script, now I use this on the table:

if(GetSelectedCount(Week) = 1,

  Only({<Month=, Week=>} Sales_WeekAccum),

    Sum({<Month=, Date=>} Sales)

)

[prabiradhikary]

There is no transaction data for week 25 ?

[sunny_talwar]

So it seems that your script is missing Sales_WeekAccum for period where Sales i not available. May be fix that in the script or for any work around it might be helpful to have a sample to look at

[ciaran_mcgowan]

Hi Sunny,

I'm unable to share any scripting/data on this one. How would it be possible to fix in the script? Inserting dummy rows if a value is missing?

[sunny_talwar]

Make sure accumulation takes place even when sales is 0 in a particular week. You might have to add all weeks for all your Employees before you perform the accumulation. I would try something like this:

Table:

LOAD Employee,

          Week

FROM ....;

Join(Table)

LOAD Distinct Week

Resident Table;

and then move forward from there....

[ciaran_mcgowan]

Thanks Sunny, I'll give that a go. Each sales week differs in the amount of weeks so I won't be able to hard code it. I'll trying populating the missing weeks on the fly for each year and let you know how I get on.

[sunny_talwar]

Yes, hard-coding won't be a good solution. I made a small mistake above, try this

Table:

LOAD Employee,

          Week

FROM ....;

Join(Table)

LOAD Distinct Week as New_Week

Resident Table;

We want to create a cross join between Employee and all possible Weeks.

[ciaran_mcgowan]

Hi Sunny, I think you are on the right track but this approach isn't feasible here. The above script is only adding each week number to each row.

45 - 50 weeks per year

100 + Employees

Between 0 & 40 sales per week (each sale is it's own row)

The result is too big and won't work. I'll let you know if I make any progress.