Question on Pivot Table

[gopikrishnach]

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.

[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]

sunny,

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

[sunny_talwar · Accepted solution]

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]

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]

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]

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?