Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Anil_Babu_Samineni

YTD Calculation

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
1 Solution

Accepted Solutions
sunny_talwar

Yes, try that

Date(Floor(Date)) as Date

or

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

View solution in original post

16 Replies
sunny_talwar

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.

Anil_Babu_Samineni
Author

Sunny,

Can you please have a look of my attachment.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

You data is not linked to each other appropriately

Capture.PNG

Anil_Babu_Samineni
Author

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Anil_Babu_Samineni
Author

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))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

Yes, try that

Date(Floor(Date)) as Date

or

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

View solution in original post

Anil_Babu_Samineni
Author

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.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

Like this?

Capture.PNG

Anil_Babu_Samineni
Author

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)