Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
eleni_theodorid
Partner - Creator
Partner - Creator

Daily Sales (set analysis issue)

Hello everybody,

I need some help with a Set Analysis Expression.

I have this straight table:

Customer is the dimension and expressions are:

1. Daily Sales : =SUM({$<[Date]={'$(vDateRef)'}>}[Sales]) (returns Sales for the selected date in the Input Box (variable)).

2. Date: =MAX( {$<[Date]={"<$(vDateRef)"},[Sales]={"<>0"}>} [Date]) (returns the previous (max) Date with data if there are no data for the selected date for each customer)

The problem is on the third expression, we want to see if there are no data in the selected date, what is the sales amount for the previous Date with data.

We have tryed to do something like this:

3. Date Sales:

=SUM({$<Date= {'$(=MAX({$<Date={"<$(=(vDateRef))"},Sales={"<>0"}>} Date))'} >}[Sales])

But as you can see it is only work for the previous date based on the selected date but not for every customer (if they don't have data exact 1 day before the selected).

Any help is appreciated!

Regards,

Helen

3 Replies
swuehl
MVP
MVP

Helen,

I think you can try something like

=SUM({$<Date= {"=aggr(if(Date=max({<Date = {'<$(vDateRef)'}>}total<Customer> Date),Date),Customer,Date)"}>} [Sales])

The search expression using advanced aggregation should limit the dates to the max dates per customer < vDateRef.

Using such a search expression might somewhat limit the performance gain when using set analysis, so you could just use

=sum(aggr(if(Date=max({<Date = {'<$(vDateRef)'}>}total<Customer> Date),Sales),Customer,Date))

instead.

Hope this helps,

Stefan

eleni_theodorid
Partner - Creator
Partner - Creator
Author

Dear Stefan,

Thank you for your quick replay, I have already try these solutions, but I don't think that anyone works fine.

(1st is the first solution you suggest (with set analysis) and 2nd the second!)

You can easy understand the problem if you check the results between this 2 columns. The fist solution work fine when select one date. e.g. When we select 14/01/2010 we take the correct result 23.56, if we don't we take 33.44?????? On the other hand, at the second solution we have 0 values which is faulse.

Thanks in advance,

Helen

swuehl
MVP
MVP

Not sure if I understand your last posting, are you referring to some tables or a sample app? I don't see any attachements.

I think we need to use

=sum(aggr(if(Date=max({<Date = {"<$(vDateRef)"}>}total<Customer> Date),Sales),Customer,Date,Sales))

to allow for multiple Sales entries per date.

There might be also some issues with date formatting, the variable date format must match your Date field date format.

Another problem might be the use of a set analysis search expression inside another set analysis search expression, which I intended to get around using single quotes, though this is not the correct way I believe.

Using just my above stated expression, we avoid embedding the set expressions, so this should be easier to handle.

Please find attached a small sample demonstrating the results.

Regards,

Stefan