Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gopikrishnach
Creator
Creator

Question on Pivot Table

Hi All,

I want to create a Automation Dashboard with task finish time and by task wise. I Created a Piovt Table with my data, but iam unable remove the duplicate date entries in the pivot table, and the source files pulling the data from log files(Operational Monitor Dashboard).

I have attached the final output screen shot and source data. Please help me how to create the dashboard as like my output screen shot.

1 Solution

Accepted Solutions
sunny_talwar

The front end is not even important here... all you need to do is fix the back end date and time because your date is actually a timestamp which is why your see repeating date (one for each of the different time). Floor removes the time component from your date and Frac removes the date component from your time.

    Date(Floor(Date)) as Date,

    Time(Frac([Finish Time])) as [Finish Time],

If you still have trouble, I will create a qvf sample... But I hope you get the idea

View solution in original post

17 Replies
sunny_talwar

Load your data like this

SET TimeFormat='h:mm:ss';

SET DateFormat='YYYY-MM-DD';

Table:

LOAD [Task Name],

    Date(Floor(Date)) as Date,

    Time(Frac([Finish Time])) as [Finish Time],

    Comments,

    [Target Time]

FROM

[..\..\Downloads\Source Data.xlsx]

(ooxml, embedded labels, table is Sheet2);

gopikrishnach
Creator
Creator
Author

sunny,

Iam unable to open the QVW, can you please provide the same in Qliksense

sunny_talwar

The front end is not even important here... all you need to do is fix the back end date and time because your date is actually a timestamp which is why your see repeating date (one for each of the different time). Floor removes the time component from your date and Frac removes the date component from your time.

    Date(Floor(Date)) as Date,

    Time(Frac([Finish Time])) as [Finish Time],

If you still have trouble, I will create a qvf sample... But I hope you get the idea

gopikrishnach
Creator
Creator
Author

Hi Sunny, can we calculate the percentage of the reload time for each month. If yes, please help me

sunny_talwar

Not sure I understand the requirement? Can you explain using an example?

gopikrishnach
Creator
Creator
Author

Hi Sunny,

Spread sheet attached with in the main description. Please review the "Final Output.xls" and help me.

sunny_talwar

May be this

=If(SecondaryDimensionality() = 0, Num(Alt(Count(Aggr(If(Max([Finish Time])<= MakeTime(6, 30), Max([Finish Time])), [Task Name], Date))/Count(Date), 0), '#.%'),

Time(Max([Finish Time])))

gopikrishnach
Creator
Creator
Author

Hi Sunny, above codes is working, but i want the total % by month wise not date wise. I tried to change the code by month, but no luck. Please help me out and do the needful.

sunny_talwar

Not sure I understand, would you be able to elaborate with screenshots?