Actual vs Budget

[tracycrown]

Dear all

I have encountered three problems, kindly help to advise urgently:

1.   Empty field in List Box ? 

2.   Synthetic keys ?

3.   Wrong information when comparing Actual against Budget ?  

Thank you

Tracy

[BrunPierre · Accepted solution]

Hi, find attached.

[tracycrown]

Dear BrunPierre

Thank you very much for resolving all my problem, you are very kind, helpful and knowledgeable. I understand that if I accept as solution now, you will not be able to see my reply and further help to answer my problems. Sorry for not doing so as I really need to learn more from you.

Kindly advise what was the problem (a blank field) for Q1 in list box and how to display all BUDGET data in Q3 for Feb 22 if BUDGET data Item0006, Item0010 & Item0018 are available in other months ?.

Thank you, Tracy 

[BrunPierre]

Hi, thread participants receive activity feed notifications based on their notification settings.

Anyways, the blank could possibly be a NULL value. To exclude these records from the model, do this in the load statement.

ActSales:
LOAD ...
FROM [Test-1.xlsx]
Where not IsNull(Item#) or Len(Trim(Item#)) > 0;

Can you elaborate, perhaps post sample data and what you expect?

[tracycrown]

Hi BrunPierre

In the past, I was told that once I click Accept as Solution, no further help will be offered. Thank you for your kind understanding why I behaved in this way.

Kindly help to answer Q4 on  how to Show Budget Unit Cost for Item0006,0010 & 0018 from Jan-22? . If not, how to change the data structure in (Test-1.xlsx) at collection stage.

Thank you very much, Tracy

[BrunPierre · Accepted solution]

@tracycrown  as below.

Bud_Sales:
LOAD ...
If(Len(Bud_Qty) = 0, Peek(NewBud_Qty), Bud_Qty) as NewBud_Qty,
If(Len(Bud_Sales) = 0, Peek(NewBudSales), Bud_Sales) as NewBudSales

FROM [Test-1(1).xlsx];

DROP Fields Bud_Qty, Bud_Sales;

RENAME Fields NewBud_Qty to Bud_Qty, NewBudSales to Bud_Sales;

[Jannet07 · Accepted solution]

Thank you so much even i was encountering same problem...

[tracycrown]

Dear BrunPierre

Thanks for your advice, I have tried but it does not work, 

Regards, Tracy