Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
sibin_jacob
Creator III
Creator 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
Specialist
Specialist

Something like??


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

sibin_jacob
Creator III
Creator III
Author

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
Specialist
Specialist

Sorry Sibin, Can you try this??

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

Anonymous
Not applicable

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