Displaying current YTD value

[wossenhh]

Hi, I have a problem showing the current value. Here is my question. Every month I am loading a payroll file for employee and with some calculation to show the Max value of employee’s on dashboard. As you can see below the base rate of employee A is 100 and for Jan, Feb, Mar there was no problem the Max YTD value shows correctly. The problem happened on April for some reason, Employee A reduced some amount (in this case $150.00) and shown as 250. So for April report QV shows, the Max value as 300 which is correct, however, I want to see the current value which is 250 for April. I was suggested to use Last Value function, and it didn’t work. Any help please?

Employee      YTD Earning      Month

A                100                           Jan

A                200                           Feb

A                300                           Mar

A                250                           Apr         

Thanks,

Wossen

[MK_QSL]

Didn't understand your actual question.

If you have month wise payment, something like below

Jan 100

Feb 100

Mar 100

Apr 50

and you want cumulative total

Create a straight table

Dimension

Month

Expressions

1) SUM(Payment)

2) RangeSum(Above(Sum(Payment),0,RowNo()))

Hope this helps...

If this will not solve your problem, kindly update your sample file along with your desired result.

[wossenhh]

Hi Manish,

Thanks for your reply. Sorry and let me clarify it again. The base salary for the employee A is $100.00. And Year-to-date salary in April supposed to be (100 + 100 + 100 + 100 ) $400. However, as I have said it previously employee A's salary has to be reversed for the amount of $150 in April and as a result of this it has shown the amount of $250 so I had the expression as Max([YTD Earning]) and was working well but not for employee A. Because it shown the amount 300. So my question is how can I able reflect the April amount for employee A? I hope it is clear now.

Thanks,

Wossen

[MK_QSL]

Can you provide little sample data in excel along with your required result ?

May be

Aggr(Max([YTD Earning]),Employee)

[wossenhh]

I can't able to use Agg function as the salary amount shows year to date. Here I attached the excel file and the required result is how to show the month of April data. Every month I load the payroll file and working hours files from Time system.

[MK_QSL]

But why is YTD Earning of A in April is 250 ?

Where you have reflected the -50 ?

[wossenhh]

Because $150 had reversed from employee A for some reason.

[MK_QSL]

Got it but how you are reflecting that in your data model. Is there any reserved amount column or not?

[wossenhh]

I don't know when you said reserved amount column. The YTD Earning column is in Fact table. In my data model I have two tables, which are Fact table and Calendar table.

[MK_QSL]

Let me explain you.

As you said  that for A in April there is an amount of 150 as reserved. You knows that... but if someone say myself, check your data model, how can get the information that this amount is reserved for A in April Month?

At this moment, you remembered but what if you have 1000 employees and there are certain amount reserved for different employees in different months.

There should be fields in your data models as below,

Employee, Month, Salary, Reserved Amount