topic Count Flag where date is greater than date X in QlikView

Count Flag where date is greater than date X

ashleywooldridg — Tue, 12 Jun 2018 06:40:46 GMT

I have a Flag [Last6TradeWeeks] which always has 42 day's marked as {'1'} the rest are {'0'}.

If I Count({1<[Last6TradeWeeks]={'1'}>}TOTAL Last6TradeWeeks) I always get back 42.

What I want to add in here is, based off a date [First Sale Date] to count all the flagged day's greater than and equal to this date.

So, if [First Sale Date] was the 01/06/2018 and [Last6TradeWeeks] ended on the 10/06/2018. Then a count of the [Last6TradeWeeks] = {'1'} should equal 10.

The issue i am coming across is that my calculations always seem to only count day's that have had a sale on it for an individual item.

Note: This value will be used to work out the average QTY sold per day within this period.

Re: Count Flag where date is greater than date X

sunny_talwar — Tue, 12 Jun 2018 11:58:07 GMT

Would you be able to share a sample to show the issue? It might be easier to help with a sample to see what you have and what you might need to change in order to get what you want

Re: Count Flag where date is greater than date X

ashleywooldridg — Tue, 12 Jun 2018 22:29:15 GMT

I have attached an example document with the issue shown.

The Table on the right shows the data as i have it in my current application.

I have thought that I may need to utilize an if statement, but am hoping there is a better way to do what i want.

Thank you for your help.

Re: Count Flag where date is greater than date X

sunny_talwar — Wed, 13 Jun 2018 08:57:59 GMT

May be using this...

=Count(TOTAL DISTINCT {1<Last6TradeWeeks={'1'}> * $<Date={'>=$(=[First Sale Date])'}>} Date)

But this will be an issue when you will have more then one item to look at... because the 10 you were counting didn't have Coffee Mug on all the rows and I had to use TOTAL function. This worked when there is only one item, but when you have more than 1, I am not sure how you would want to see

Re: Count Flag where date is greater than date X

ashleywooldridg — Thu, 14 Jun 2018 01:39:13 GMT

Thank you Sunny, I managed to get this at one point, but found the issue you pointed out.

I have gone another direction at the moment, not sure if it is the best method, but will use IF statements to make allowances where required.

The below gives me the required number of total day's between certain date's and i'll use IF based off the [First Sale Date] to determine what to subtract the Max([Sale Date]) by, if a sale has been made.

=(Floor('$(=Max({1<[Last6TradeWeeks]={'1'}>} [Sale Date]))') - Floor([First Sale Date]))+1