Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
harinikonka
Contributor II
Contributor II

Quarter totals issue

Hi All,

I’m trying to calculate the number of days it took from “already completed” type to “will complete” type. It’s calculating the number of Days correct but when I try to plot this data cross quarters it’s not calculating it correctly (it’s showing the same value across all the quarters). Please can someone help me with this issue. Attached qvw and excel sample data for reference.

To make it clear:

In the attached excel and qvw for ID:434 the number of days it took from already completed to will complete (10/8/2015-2/11/2016)= 126 days-  this is correct

But when I plot it in the bar chart it’s showing Q42015-126, Q12016-126, Q22016-126- Wrong

Expected Result:  for Q42015-84

                                     Q12016-41

                                      Q2016- 0

1 Solution

Accepted Solutions
Nicole-Smith

You already have it taking Type into account in your load into Main (lines 14 and 15):

Main:

LOAD ID,

     Product,

     [Line Item],

     Type,

     Status,

     [Approval date],

     [sales rep],

     day([Approval date]) as Day,

     month ([Approval date]) as Month,

     Year ([Approval date]) as year,

     IF(Len([Approval date])>0, 'Q' & ceil( month(date([Approval date])) / 3)) as quarter,

     IF(Len([Approval date])>0, 'Q' & ceil( month(date([Approval date])) / 3)) & Year ([Approval date]) as Quarter_Year,

     if([Type]='already completed',[Approval date]) as [already completed Date],

     if([Type]='will complete',[Approval date]) as [will complete Date]    

FROM Test2.xlsx (ooxml, embedded labels, table is Sheet1);

So I think all you need is to add where clauses in the Tmp table for Status and Type (lines 5-6 and 13):

Tmp:

LOAD ID,

     [Approval date]

RESIDENT Main

WHERE Status='Approved'

     AND MATCH([Type], 'already completed', 'will complete');

LEFT JOIN (Tmp)

LOAD ID,

     MIN([will complete Date]) AS [min will complete date],

     MIN([already completed Date]) AS [min already completed date]

RESIDENT Main

WHERE Status='Approved'

GROUP BY ID;

LEFT JOIN (Main)

LOAD ID,

     [Approval date],

     IF(InQuarter([min will complete date], [Approval date], 0) AND InQuarter([min already completed date], [Approval date], 0), [min will complete date] - [min already completed date],

          IF(InQuarter([min will complete date], [Approval date], 0), [min will complete date] - QuarterStart([Approval date]),

          IF(InQuarter([min already completed date], [Approval date], 0), DayStart(QuarterEnd([Approval date])) - [min already completed date]))) AS Days

RESIDENT Tmp;

DROP TABLE Tmp;

I'm unable to test this with the "apple" example that you're referencing, as there is no "apple" data in your example data.

View solution in original post

7 Replies
Nicole-Smith

This load script should do the trick:

Tmp:

LOAD ID,

[Approval date]

RESIDENT Main;

LEFT JOIN (Tmp)

LOAD ID,

MIN([will complete Date]) AS [min will complete date],

MIN([already completed Date]) AS [min already completed date]

RESIDENT Main

GROUP BY ID;

LEFT JOIN (Main)

LOAD ID,

[Approval date],

IF(InQuarter([min will complete date], [Approval date], 0) AND InQuarter([min already completed date], [Approval date], 0), [min will complete date] - [min already completed date],

IF (InQuarter([min will complete date], [Approval date], 0), [min will complete date] - QuarterStart([Approval date]),

IF (InQuarter([min already completed date], [Approval date], 0), DayStart(QuarterEnd([Approval date])) - [min already completed date]))) AS Days

RESIDENT Tmp;

DROP TABLE Tmp;

I've also attached the same example from your original post with this code included and working.

vvira1316
Specialist II
Specialist II

HI,

Isn't this same as Subtract Dates?

harinikonka
Contributor II
Contributor II
Author

Hi Vijay,

Not exactly same,  now it involves bit more complexity what ever Nicole did is correct but i didn't provide complete data set/requirement  first time. Thanks,

Nicole-Smith

You already have it taking Type into account in your load into Main (lines 14 and 15):

Main:

LOAD ID,

     Product,

     [Line Item],

     Type,

     Status,

     [Approval date],

     [sales rep],

     day([Approval date]) as Day,

     month ([Approval date]) as Month,

     Year ([Approval date]) as year,

     IF(Len([Approval date])>0, 'Q' & ceil( month(date([Approval date])) / 3)) as quarter,

     IF(Len([Approval date])>0, 'Q' & ceil( month(date([Approval date])) / 3)) & Year ([Approval date]) as Quarter_Year,

     if([Type]='already completed',[Approval date]) as [already completed Date],

     if([Type]='will complete',[Approval date]) as [will complete Date]    

FROM Test2.xlsx (ooxml, embedded labels, table is Sheet1);

So I think all you need is to add where clauses in the Tmp table for Status and Type (lines 5-6 and 13):

Tmp:

LOAD ID,

     [Approval date]

RESIDENT Main

WHERE Status='Approved'

     AND MATCH([Type], 'already completed', 'will complete');

LEFT JOIN (Tmp)

LOAD ID,

     MIN([will complete Date]) AS [min will complete date],

     MIN([already completed Date]) AS [min already completed date]

RESIDENT Main

WHERE Status='Approved'

GROUP BY ID;

LEFT JOIN (Main)

LOAD ID,

     [Approval date],

     IF(InQuarter([min will complete date], [Approval date], 0) AND InQuarter([min already completed date], [Approval date], 0), [min will complete date] - [min already completed date],

          IF(InQuarter([min will complete date], [Approval date], 0), [min will complete date] - QuarterStart([Approval date]),

          IF(InQuarter([min already completed date], [Approval date], 0), DayStart(QuarterEnd([Approval date])) - [min already completed date]))) AS Days

RESIDENT Tmp;

DROP TABLE Tmp;

I'm unable to test this with the "apple" example that you're referencing, as there is no "apple" data in your example data.

View solution in original post

harinikonka
Contributor II
Contributor II
Author

Hi Nicole,

I’m sorry to trouble you, I was able to fix  all the issues in my app except one. Please can you help me.

In the attached qvw and excel for the product “ REB”  it’s not showing any data for Q12014 and Q22014. I think it’s due to the InQuarter function, I tried to play around with that but still didn’t work. Please help me. Thank you so much  for your help,

Harini

Nicole-Smith

I changed the load script and the chart dimension/expression.  Your example file is attached with my changes.

harinikonka
Contributor II
Contributor II
Author

Thank you so much Nicole. Really appreciate it.