topic Re: Calculating previous date data in QlikView

Calculating previous date data

Sun, 29 Jan 2017 19:55:32 GMT

I am trying to determine a count of Issue ID of a previous Load Date. The Load Dates are Thursday dates. I have attached a file with the Load Date and the Issue IDs I am working with. I do have duplicates in the source data, so I have been trying to use DISTINCT in the formula.

This is what I am trying so far and it is only giving me the current value:

=Num(count(Distinct{$<[Load Date]={">=$(=WeekStart(Addweeks(Today(),-1)))<=$(=WeekEnd(AddWeeks(Today())))"}>} [Issue ID]),'0')

The -1 Total should be the total from the previous week, but as you can see, it is the current total repeated.

My load statement for the Load Date is ensuring that the Load date is an actual Date:

LOAD Date(Num([Load Date]), 'MM/DD/YYYY') AS [Load Date],

Any help would be greatly appreciated!

Re: Calculating previous date data

vishsaggi — Sun, 29 Jan 2017 21:11:47 GMT

Your excel sheet has not got the field Total? Can you reattach the excel with your Total field values?

Re: Calculating previous date data

Sun, 29 Jan 2017 21:34:45 GMT

Hi Vishwarath:

I am doing a DISCTINCT Count on Issue ID, which is Column A in the spread sheet. The screen shot in the original post shows what the counts should be for each Load Date (827 at 12/15/2016, 836 at 12/22/2016, etc)

Re: Calculating previous date data

vishsaggi — Sun, 29 Jan 2017 22:02:35 GMT

Oh ok got it. But i doubt we have any function named AddWeeks here so the expression would not calculate any there? You just want to calculate previous week count?

Re: Calculating previous date data

vishsaggi — Sun, 29 Jan 2017 22:11:48 GMT

I am not sure let me know if this is what you are looking for ?

Re: Calculating previous date data

Sun, 29 Jan 2017 22:21:43 GMT

Yes, that is exactly what I am looking for! How did you calculate that?

Re: Calculating previous date data

vishsaggi — Sun, 29 Jan 2017 23:39:47 GMT

I have just used these two expressions.

Dim: LoadDate

Exprs:

= Count(DISTINCT IssueID)

= Above(Count(Distinct IssueID))

Re: Calculating previous date data

Mon, 30 Jan 2017 11:20:54 GMT

thank you so much! One last question, if I don't want to show Load Date in the table, and just have the straight table that has current count Issue ID and Last Load Date Issue ID, how would I go about getting that?

Re: Calculating previous date data

vishsaggi — Mon, 30 Jan 2017 12:34:16 GMT

I did not get you. You want to hide the loaddate column and display only the count or you want to display IssueID as well?

To hide the load date try below.

Re: Calculating previous date data

Mon, 30 Jan 2017 12:39:59 GMT

I am actually not wanting all of the Load Dates to show at all. I need to have 1 Row, that ultimately shows current Load Date Issue Count , Previous Load Date Issue Count and I have an Arrow to show if Current Issue increased or decreased from last Load Date.

Re: Calculating previous date data

vishsaggi — Mon, 30 Jan 2017 12:53:39 GMT

How did you get 914 there? Is that the correct total for current load date? For me the current load date count is coming 808? What is the total you are expecting in your -1Total column ?

Re: Calculating previous date data

Mon, 30 Jan 2017 12:59:43 GMT

That is data from something else. I would need to see 808 as the current count and 826 as previous count. so then the arrow would be green and going up. (I have the calculation to do the arrow, but cannot get the previous count.

Re: Calculating previous date data

vishsaggi — Mon, 30 Jan 2017 14:16:51 GMT

Oh got it. Ok will work on it once i reach office and get back.

Re: Calculating previous date data

Mon, 30 Jan 2017 14:30:16 GMT

Thank you so much!

Re: Calculating previous date data

vishsaggi — Mon, 30 Jan 2017 14:33:53 GMT

Try this and let me know.

Current Expr: = Count(DISTINCT {< LoadDate = {'$(=Max(LoadDate))'} > } IssueID)

Previous Expr: = Count(DISTINCT {< LoadDate = {'$(=Max(LoadDate,2))'} > } IssueID)

Re: Calculating previous date data

Mon, 30 Jan 2017 15:34:59 GMT

Perfect!! Thank you so very much!