topic Re: Re: Displaying current YTD value in QlikView

Displaying current YTD value

wossenhh — Thu, 01 May 2014 16:01:29 GMT

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

Re: Displaying current YTD value

MK_QSL — Thu, 01 May 2014 18:06:40 GMT

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.

Re: Displaying current YTD value

wossenhh — Thu, 01 May 2014 18:26:54 GMT

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

Re: Displaying current YTD value

MK_QSL — Thu, 01 May 2014 18:29:35 GMT

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

May be

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

Re: Re: Displaying current YTD value

wossenhh — Thu, 01 May 2014 18:44:55 GMT

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.

Re: Displaying current YTD value

MK_QSL — Thu, 01 May 2014 18:49:48 GMT

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

Where you have reflected the -50 ?

Re: Re: Displaying current YTD value

wossenhh — Thu, 01 May 2014 18:53:44 GMT

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

Re: Displaying current YTD value

MK_QSL — Thu, 01 May 2014 19:07:26 GMT

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

Re: Re: Displaying current YTD value

wossenhh — Thu, 01 May 2014 19:13:23 GMT

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.

Re: Re: Displaying current YTD value

MK_QSL — Thu, 01 May 2014 19:31:56 GMT

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

Re: Re: Displaying current YTD value

wossenhh — Thu, 01 May 2014 19:43:45 GMT

Hi Manish,

I hope you meant reversed when you say reserved. This was happened just for this month, and the money has reversed from employee A. Does this might happen in the future? who knows it might....but I am sure there will be a trick to reflect the amount in QV.

Re: Displaying current YTD value

Thu, 01 May 2014 19:52:59 GMT

not sure what exactly your data looks like, assuming its same as you provided sample and your chart is with dimension employee and expression as latest salary

exp: max({<Month={"$(=Date(max(Date#(Month,'MMM')),'MMM'))"}>}[YTD Earning])

Re: Re: Displaying current YTD value

wossenhh — Thu, 01 May 2014 20:15:21 GMT

Thanks My QV - the dimension I have is Fiscal month and some calculated dimensions just to get department, employee so on. I tried what you suggested and no luck.