topic Re: YTD Calculation in QlikView

YTD Calculation

Anil_Babu_Samineni — Wed, 25 Nov 2020 16:16:04 GMT

Dear Friends,

I am trying to create one Pivot table. Here, i want to calculate Week and YTD using My Date field with the calculation

For Week Calculation, I am using this

=sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'} >} S_COUNT)

For Year Calculation, I am using this

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} S_COUNT)

My Master Calendar is

MinMaxDate:

Load

//Num(Yearstart(Min(S_CREATED_DATE))) as MinDate,

Num(Min(S_CREATED_DATE)) as MinDate,

Num(Max(S_CREATED_DATE)) as MaxDate

Resident Facts;

Let vMinDate = Peek('MinDate',0,'MinMaxDate');

Let vMaxDate = Peek('MaxDate',0,'MinMaxDate');

Drop Table MinMaxDate;

Temp_calendar:

load $(vMinDate) + Recno() - 1 as Date autogenerate $(vMaxDate)-$(vMinDate) +1;

Master_calender:

Load //MonthName(Date) as MonthYear,

Date#(Date(Date),'DD-MM-YYYY') as Date,

//time(date#( Date , 'YYYYMMDDhhmmss'), 'DD:MM"YYYY hh:mm:ss') as Date,

Year(Date) as Year,

Day(Date) as Day,

Week(Date) as Week,

Month(Date) as Month,

'Q' & Ceil(month(Date)/3) as Quarter

Resident Temp_calendar;

drop Table Temp_calendar;

So, Now i am expecting only Week and Year Calculation using Sum(S_COUNT)

Please help me

Re: YTD Calculation

sunny_talwar — Thu, 14 Jul 2016 09:57:49 GMT

Where is DateNum field in your master calendar?

Also, I think it never makes sense to use Date#(Date()) function in this order. AFAIK, the order should be 1st Date() and then Date#() because Date# is an interpretation function. If your date is already recognized using Date(), then you probably won't even need Date#() after it.

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 14 Jul 2016 10:11:39 GMT

Sunny,

Can you please have a look of my attachment.

Re: YTD Calculation

sunny_talwar — Thu, 14 Jul 2016 10:13:48 GMT

You data is not linked to each other appropriately

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 14 Jul 2016 10:18:11 GMT

Sunny, In my application it's connected with 1-1 Map. But unfortunately, as per our company policy we cant share the data.

This Case, Can you please have a look of Pivot table. In that, i want to calculate Week and YTD. In fact, My master calendar is not good. Can you please help me for this.

Thanks

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 14 Jul 2016 11:56:17 GMT

Sunny,

I got it.

Date#(Date()) -- What i need to written on this?

My Date Format showing MM/DD/YYYY HH:MM:SS TT

Might be this -- date(floor(Date))

Re: YTD Calculation

sunny_talwar — Thu, 14 Jul 2016 12:03:40 GMT

Yes, try that

Date(Floor(Date)) as Date

Date(Floor(Date#(Date, 'MM/DD/YYYY hh:mm:ss TT'))) as Date

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 14 Jul 2016 12:08:57 GMT

Sunny, Got it.

I have one more issue.

I do have one chart(For Ref PFA)

So here i have list box also. When i want to click any year i want to show the selected data and remaining all i want to hide.

But, Here i want to show default all graph. I want to pop up only selected Year and remaining Data should be null.

If i select 2010 --> I want to show 2010, 2011, 2012, 2013 Data as default. But, Only 2010 Data should be visible left all need to hidden.

Re: YTD Calculation

sunny_talwar — Thu, 14 Jul 2016 12:17:43 GMT

Like this?

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 14 Jul 2016 12:28:37 GMT

Yes, Can you plz explain me

You are taken only this Round(Avg(Sales)) + Sum({1} 0)

What is the use of Sum({1} 0)

Any changes

Re: YTD Calculation

sunny_talwar — Thu, 14 Jul 2016 12:33:52 GMT

That part is to force a 0 regardless of selection to those dimension values which are not selected. This happens because of {1} in there.

I also unchecked 'Suppress Zero-Values' on the presentation tab to show 0 values for those dimensions which are not selected.

Does this make sense?

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 14 Jul 2016 12:36:07 GMT

So, Here {1} is nothing but {Sales}

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 14 Jul 2016 18:56:44 GMT

Sunny,

This scenario I want to highlet whole we click the year

So, I want to click the year I want to highlet for selection left all need to see hidden graphs.

But, I want to show whole data. Like attachment

Cab you please help me this.

Re: YTD Calculation

sunny_talwar — Thu, 14 Jul 2016 18:59:28 GMT

Do you mean Year or City? What is your current expression?

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 14 Jul 2016 19:01:16 GMT

That image is for sample. So, you already shared me that application I want to show sales by year selection.

Please send me the expression

Re: YTD Calculation

sunny_talwar — Thu, 14 Jul 2016 19:06:25 GMT

What expression Anil, I am not sure what you mean my friend

Re: YTD Calculation

Anil_Babu_Samineni — Thu, 14 Jul 2016 19:09:52 GMT

Sunny,

You shared me one application before right

In that, my client want to show all year data then once we click the year I want to highlight that selection year. Remaining graphs are hidden.

Did you understand my question. Please check before conversation