Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Dynamic and default YTD Qlik Sense

Hi 

I have a requirement where I have a yearmonth called [FS] field (201706) i.e  min  yearmonth period available 

I have 2 requirements -

 

one is 

if(year=2017, min value = count(' 2017 jun', id) max value=  count (selected value in the filter, id)

if (year=current  , current year ytd min max max values

else whatever the value is selected check the year and get the YTD )

 

sample data

FScount
201706100
20170720
201708500
201709189
201710678
201711765
201712984
201801234
201802985
201803678
201804436
201811458
201812654
201810780
201805398
2018071298
2018091287
201806103
201808876
201901653
201902765
201903765
201904564
20190598
201908890
2019074532
201906543
201909653
201910873
2019119853
202001986
202002876
201912128
202001345
202002765

 

the other one is - 

by default it has to show 

starting value - 128(201912) - which is previous year month end value - Ending value -765 ( 202002)

if(year =2017

Starting value = 100(201706)- that is is least data  point record  available in 2017 EV= selected field corresponding value

else 

if  201809  is selected 

sv= which is previous year month end value- 201712 /EV=year end of 2018 value 

if(2019 is selected  sv= which is previous year month end value- 201812/ EV=year end of 2019 value

 

and so on 

 

 

Labels (1)
3 Replies
Highlighted
Creator II
Creator II

Re: Dynamic and default YTD Qlik Sense

Hi,

I have a doubt in your second condition,

if(year =2017

Starting value = 100(201706)- that is is least data  point record  available in 2017 EV= selected field corresponding value

Rest of your condition says whatever the monthyear you select it show previous year Month End as Start date and Selected Date maximum Monthyear is your End Date,

For example  :

201908 selected Then SV= 201812 and EV=201912

Please have a look at the below Code which will satisfy your all condition except the above one

SV= Min({1<FS_Date={"$(=Date(YearEnd(Addmonths(Max(FS_Date),-12)),'YYYYMM'))"}>}count)

EV=Min({1<FS_Date={"$(=Date((Max(FS_Date)),'YYYYMM'))"}>}count)

 

Condition1 Solution :

Minimum Value = Min({<Year={"$(=max(Year))"},
FS_Date = P({1<
Year = {"$(=Max(Year))"},
FS_Date = {"<=$(=min(FS_Date))"}
>} FS_Date) >}count) 

 

Maximum Value= 

Max({<Year={"$(=max(Year))"},FS_Date = P({1<
Year = {"$(=max(Year))"},
FS_Date = {"$(=max(FS_Date))"}
>}
FS_Date)>}count)

May it helps you

FS_Date calculated in backend =

 

Test:
load * inline
[
FS ,count
201706 ,100
201707 ,20
201708 ,500
201709 ,189
201710 ,678
201711, 765
201712, 984
201801 ,234
201802, 985
201803, 678
201804, 436
201811, 458
201812 ,654
201810, 780
201805 ,398
201807 ,1298
201809 ,1287
201806 ,103
201808, 876
201901, 653
201902 ,765
201903 ,765
201904 ,564
201905, 98
201908 ,890
201907, 4532
201906 ,543
201909 ,653
201910, 873
201911, 9853
202001, 986
202002, 876
201912 ,128
202001 ,345
202002, 765
];

NoConcatenate
Test1:
Load *,
Year(FS_Date) as Year;
Load *,
Date(MAkedate(left(FS,4),Right(FS,2) ),'YYYYMM')as FS_Date
Resident Test;

drop Table Test;
exit Script;

 

Highlighted
Creator
Creator

Re: Dynamic and default YTD Qlik Sense

Thankyou Nisha, I will try the  logic .Is there a particular reason  FS has to be converted to date in backend .

 

Second condition requirement is correct. 

 

Highlighted
Creator
Creator

Re: Dynamic and default YTD Qlik Sense

This is syntax  for second requirement min value takes- and gives a random number but.

 

Capture.PNG

 

So I tried changing the code a little 

 

Capture.PNG

year= 2019 

and FSdate - it has to take is max month of year 2019. but it picks the overall min date value .