Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Type based on the date calculation

Hi -  I have data as below.

Date                             Type

1/2/2020                   Main

3/2/2020                   Sub

5/4/2020                   Main

 

My logic is to look for date that is closer today, but < today.So, I have as below..

max(if(Date < today(),Date))

Now I need to add the Type for this date as well...so it should be, 3/2/2020 - Sub.

 

Thank you much.

 

 

5 Replies
Highlighted
Creator
Creator

@Vegar , any thoughts on this ?

Highlighted
Master
Master

Hi you can use this,

 

Temp:
LOAD *
,Date(Date#(Date,'MM/DD/YYYY'),'DD-MM-YYYY') as Date_Create
 INLINE [
    Date             
    1/2/2020                      
    3/2/2020                   
    5/4/2020                   
];

Temp_Max:
Load Max(Temp) as Sub;
Load 
Date(if(Date_Create < today(),Max(Date_Create) )) as Temp
Resident Temp Group by Date_Create;

Let vMaxSub=Peek('Sub');

Data:
Load Date_Create,
If(Num(Date_Create)=$(vMaxSub),'Sub','Main') as Type
Resident Temp;

DROP Table Temp,Temp_Max;

 

HirisH_V7_0-1588351293218.png

 

HirisH
“Aspire to Inspire before we Expire!”
Highlighted
Partner
Partner

Did @HirisH_V7  respons solve your issue?

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Creator
Creator

@Vegar  - Thank you.. I was able to get that thru the below expression.

FirstSortedValue(Name, -if(Date<Today(),Date))

but now I want to Cinactenate Name as I have same date with Different Names...I tried below,..no luck

FirstSortedValue(conact(Name,'-'), -if(Date<Today(),Date))

Highlighted
Partner
Partner

Hi,

Create a key at back end for name and date and use that.

Regards,

Shivani Sapkale