set analysis 2 - dynamic dimension?

[jakobjosef]

I already thought that the AGGR function should help, but unfortunately it did not.

Here is some easy data: (Document attached as well)

Gruber and Jeremiah never sold something. I want this view:

Employee    Working hours

Abu              48

Jack              48

John            48

Gruber & Jeremiah never sold anything, so I dont want them in the List.

fyi: "if(SalesAmount>0,Sum(WorkingHours)" doesn't work, cause QV just sums  up (ONLY) the days where something got sold.

I am looking forward to your proposals.

Greetings
Jakob

[vishsaggi]

But there are few missing sales for employees on few dates, you want to consider hours for those dates with out sales amount?

Try this as your expression:

= Sum({<[Sales Amount] = {">0"} >} [Working hours])

[jakobjosef]

Thank you for your answer, Vishwarath.

The sales are not missing, there are just no sales on this dates. Employees do not sell something every day.

So yes, I want to consider hours for those dates without sales amont.

I tried your proposal, unfortunately it just sums up the hours of days, on which something got sold. But I want to sum up ALL hours, if there was a sale at any time.

[chrismarlow · Accepted solution]

After an initial false start - how about;

If(aggr(sum([Sales Amount]), Employee, MthYear)>0,Sum(Total <Employee, MthYear> [Working hours]),0)

Where MthYear is a calculated field added at load.

Regards,

Chris.

[vishsaggi]

Try this in your straight table

Add Dim: Employee

Expr:

= Sum(IF(Aggr(NODISTINCT Sum([Sales Amount]), Employee)=0, 0,[Working hours]))

[jakobjosef]

Hi Vishwarath,

thank you for your answer. With your expression, all values are summed up (not just the ones of the month I seleced), but i know how to deal with this problem.

Thank you for your help

Greetings
Jakob

[jakobjosef]

Hi Chris,

your solution works as well, thank you for your help, I really appreciate it.

Have a nice day

Jakob