Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gopikrishnach
Contributor

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

Re: Question on Pivot Table

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

17 Replies

Re: Question on Pivot Table

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
Contributor

Re: Question on Pivot Table

sunny,

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

Re: Question on Pivot Table

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
Contributor

Re: Question on Pivot Table

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

Re: Question on Pivot Table

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

gopikrishnach
Contributor

Re: Question on Pivot Table

Hi Sunny,

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

Re: Question on Pivot Table

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
Contributor

Re: Question on Pivot Table

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.

Re: Question on Pivot Table

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

Community Browser