Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
sibin_jacob
Contributor III

Set Analysis issue

Hi All,

I need a help on the below set analysis.

I have two columns, one is SALES_DATE and another PURCHASE_DATE.

User will select the PURCHASE_DATE in the dashboard

I need to show the sales in the table of which date is selected by the user.

AGGR(only({<SALES_DATE=P(PURCHASE_DATE)>} SALES_DATE), SALES_DATE)

I am using the above formula for the dimension, which is working fine if I have sales date for the Purchase date user selected

For Example : User selected 17/01/2017 in the Purchase date list box, but I don’t have sales in that date so I need to show the near previous sales data in the table, that is 15/01/2017


Thanks,

Sibin Jacob. C

4 Replies
laddu_927
Valued Contributor

Re: Set Analysis issue

Something like??


AGGR(only({<SALES_DATE={="max(if(SALES_DATE<PURCHASE_DATE,PURCHASE_DATE))"}>} SALES_DATE), SALES_DATE)

sibin_jacob
Contributor III

Re: Set Analysis issue

Hi Siva,

It is not working.

If user selecting 17/01/2017 in the PURCHASE_DATE listbox, which is not present in SALES_DATE.

SO I need the near previous date from SALES_DATE column.

I need Max(SALES_DATE),considering the selected PURCHASE Date


Thanks,

Sibin Jacob. C

laddu_927
Valued Contributor

Re: Set Analysis issue

Sorry Sibin, Can you try this??

aggr(Date(MAx(if(SalesDate<PurchaseDAte,SalesDate)),'DD/MM/YYYY'),PurchaseDAte)

robin_hausdoerfer
Valued Contributor III

Re: Set Analysis issue

=date(aggr(max(if(SALES_DATE<=PURCHASE_DATE,SALES_DATE)),PURCHASE_DATE
//put additional Dimensions here like ,Customer,Article...if needed
))