Count Unique based on date

[jorgie]

Hi to all!

I am using the following function

Count( Distinct If( Sales > 0,[Customer] ) )

I get data like that the total of unique customers is 100, where 80 are category A and 60 are category 40.

I tried to find a way that based on the selected date range, will count only the least recent date that Sales>0.

In that way I will be able to count new.

Thanks to @hic  (who has deep knowledge of qlik) I had his following proposal

Count(

             {<Customer={"=Sum(Sales)>0"}>} distinct
   Aggr(
              If(Min(total <Customer> Date)=Date, Only(Customer)),
              Customer,
              Date)  )

Unfortunately it didn't work as I hoped.

Seems to lose some counting as it seems below

Also, in case I select only one month date range (or filter month) I should have same values

I would appreciate any help

thank you in advance

[Kushal_Chawda · Accepted solution]

@jorgie  try to move Sales>0 aggregation inside inner aggregation while finding Min Month

=count(aggr(If( Month=min(total <CustID> aggr(if(sum(SALES)>0, Month),CustID,Month)),CustID),CustID,Month))

[jorgie]

thank u @Kushal_Chawda seems  to  have the effect i am expecting (i have a small dif 1 to 350), I will do some extended tests and I will give feedback.

Would you be kind enough to explain the logic by part?

It would be very helpful to understand it. Or a link to read in depth about it.

thank you for your time

[jorgie]

so I used it for Month but and for Date.

Selecting one Month the results where the following with some diffs

also

and

or for Month=0 and for Date or distinct is 1

Hope it helps

[Kushal_Chawda]

@jorgie  As you require to pick Min month where sales is >0, first condition to check for is which months have sales greater than 0 for each customer, so we have aggr(if(sum(SALES)>0, Month),CustID,Month). This produces virtual table in memory with Customer, Month(all customer & all months) where sale is greater than 0. Now, we need Minimum month out of this so we have min function on top of that as mentioned below. Total qualifier within Min function makes sure that Min month is calculated for each customer dimension value.

min(total <CustID> aggr(if(sum(SALES)>0, Month),CustID,Month)

Once we get the Min month for each customers, we need to match it with each row of Customer and Month dimensions within straight table, hence we have If (Month = min(...) condition which should be grouped by Customer & Month( because we have aggregation function inside if [Min(..)]), so we have aggr(If(Month=min(..)).

Final aggregation will be count of customers which completes the entire expression. Hope this helps.

[jorgie]

thanx!

I don't want to take advantage of your help, but I try hard to learn the logic (in general)

I would really appreciate if you could analyze the logic (attached) like

thanks again @Kushal_Chawda 

PS Any advice where to learn more about expressions?

[Kushal_Chawda]

@jorgie Probably my presentation skills are not good, but this is really good visual way to understand the expression. If you want to learn more expression, you need to practice. Take any medium to complex expression and try to breakdown expressions part by part and see what results you get. That way you will learn the fundamentals. Before to that always start with basics so go to Qlik help sites and try to learn functions and set analysis. Once you understand, try to practice more. Qlik community is the good platform which gives tons of scenarios to practice.