Need to find Previous days Duration based on Current date workflow names

[Rajesh]

Hi All,

I am trying to create a Table in QS to find out the "> 20"(Duration) workflow names and get previous 5 days Duration on same Current date workflow names( challenge: its like trace back Duration on Current date same workflow names.)

Please see below screenshot required output.

Dim: Workflow name, Session name, start time, end time

Measure: =Sum({<duration={">20"},Date={"$(=Date(Today(),'MM/DD/YYYY'))"}>}Duration)</duration={">

Not able to get desired output.

Kindly can any one suggest the solution.

Thanks

Rajesh

[Rajesh]

Measure is : =Sum({<Duration={">20"},Date={"$(=Date(Today()-1,'MM/DD/YYYY'))"}>}Duration)

[tresesco]

When you post such query, it is always better to post with a sample app where others can have a look at the preventative data and understand the issue and required output better and that way you help others to be helped. Otherwise, there would possibly be many follow up questions to understand your data and requirement - which might not interests many here like me.

Therefore, try to share a sample app and explain your expected output there. 

[Rajesh]

Hi Tresesco,

Please find attached sample app.

the required out put is like below.

i am able to get Current date data but not previous date data as expected.

Please let me know if required Sample QVD.

Can you please suggest the solution.

Thanks

Rajesh.

[tresesco]

I see an expression in your chart as:

=Sum({<Duration={">20"},Date={"$(=Date(Today()-2,'MM/DD/YYYY'))"}>}Duration)

And it seems working... Isn't it? Or, could you please what exactly is not working (not getting right value or something else)

[Rajesh]

Hi Tresesco,

Yes, As i mentioned the Expression is working correct for Current Date data

Sum({<Duration={">20"},Date={"$(=Date(Today(),'MM/DD/YYYY'))"}>}Duration) ( as inserted photo image table)

Based on this Current date data previous days data should display with out changing the Workflow names.

its kind of trace back the Workflows(Jobs) duration.(How was the previous dates Duration for particular Current date workflows)

if i try to apply : Sum({<Duration={">20"},Date={"$(=Date(Today()-3,'MM/DD/YYYY'))"}>}Duration)   

                                 Sum({<Duration={">20"},Date={"$(=Date(Today()-2,'MM/DD/YYYY'))"}>}Duration) 

                                Sum({<Duration={">20"},Date={"$(=Date(Today()-1,'MM/DD/YYYY'))"}>}Duration)               

i will not get previous dates desired output.

for u r reference the inserted image is took from sql server out put.(Achieved in)

please find attached SQLServer query.

Thanks

Rajesh

[tresesco]

Have a look at the screenshot of your app and this is not for current date but for -2 date expression which seems working:

[Rajesh]

For Current date data is not available right now Once our ETL load compltes data will be available after that( lets say after 8PM IST).

u can get yesterday data Put "-1" in the expression, U can assume that u have current date data for given expression.

=Sum({<Duration={">20"},Date={"$(=Date(Today(),'MM/DD/YYYY'))"}>}Duration)

Please let me if it not clear.