Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Logic

Hi,

How can we minus the dates .ie in below excel i want to do minus   like [Current-Date -Joining-Date] then average .


Requirement:

i want to know in how many days an employee is moving to higher position like [L1-L2]

Output :average days took to go to L1-L2 Position


Plz Help me

4 Replies
swuehl
MVP
MVP

Load the data from your excel file:

DATA:

LOAD [Emp-Id],

     [Joining-Date],

     Position,

     [Current-Date],

     [Current-Positin]

FROM

[Analysis.xlsx]

(ooxml, embedded labels, table is Sheet1);

Then create a text box with this expression to calculate the average days

=Avg( [Current-Date]-[Joining-Date] )

edit: simplified the expression

Not applicable
Author

thanku Swuehi.

in text box i want like this:

how many days it  required  for changing Designation like L1-L2 only in excel i have L2-L3 so on but i want only for L1-L2

HirisH_V7
Master
Master

Hi,

you can use some thing like this,

=if(GetFieldSelections([Emp-Id]),[Joining-Date]    &   '         '& [Current-Date] &   '         ' &    Count([Current-Date]-[Joining-Date])    &   ' Days Difference'  &    Position   &   '         '  

&    [Current-Positin] &   '         '  ,

'Select EMPID')

Hope this helps,

Thanks,

hirish

HirisH
swuehl
MVP
MVP

Ok, I think I understand now better.

Just use above expression in a text box, then select L1 in Joining Position and L2 in Current Position list boxes?

If you want a chart to show all upgrades, maybe create a straight table chart with two dimensions:

[Position]

=if(Mid([Current-Positin],2,1)>Mid([Position],2,1),[Current-Positin])

Enable 'Suppress when value is NULL' for second dimension on dimension tab.

Use as expression again:

=Avg( [Current-Date]-[Joining-Date] )

If you only want to show a fixed KPI for L1 to L2 upgrades in a text box:

=Avg({<[Current-Positin] = {"L2*"}, Position = {"L1*"}>} [Current-Date]-[Joining-Date] )