Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Jesh19
Creator II
Creator II

Show last 5 periods based on current period

Hi All,

I need help in showing last 5 periods based on current period value in Qlikview Pivot Chart.

Dimensions -> District, Period

Exp -> Num((Sum({<Year=,Month=,Period=,Start_Date=,Start_Date={">=$(=Date(Max(Start_Date)-85))<=$(=Max(Start_Date))"}>}f_appt_set)
/
Sum({<Year=,Month=,Period=,Start_Date=,Start_Date={">=$(=Date(Max(Start_Date)-85))<=$(=Max(Start_Date))"}>}f_engaged)
),'#,##0.#%')

District24Sep2020-8Oct20209Oct2020-21Oct202022Oct2020-4Nov20205Nov2020-18Nov202019Nov2020 - 2Dec2020
D150%20%30%90%45%
D240%60%70%80%8%
D325%8%30%20%10%
D410%81%72%66%25%

 

I have a button "Below 10%" --> When I click this button, it should only give me D2 and D3 records...how to acheive this?

My current logic is giving me 

If(
(Num((Sum({<Year=,Month=,Period=,Start_Date=,Start_Date={">=$(=Date(Max(Start_Date)-85))<=$(=Max(Start_Date))"}>}f_appt_set)
/
Sum({<Year=,Month=,Period=,Start_Date=,Start_Date={">=$(=Date(Max(Start_Date)-85))<=$(=Max(Start_Date))"}>}f_engaged)
),'#,##0.#'))<=0.10,

Num((Sum({<Year=,Month=,Period=,Start_Date=,Start_Date={">=$(=Date(Max(Start_Date)-85))<=$(=Max(Start_Date))"}>}f_appt_set)
/
Sum({<Year=,Month=,Period=,Start_Date=,Start_Date={">=$(=Date(Max(Start_Date)-85))<=$(=Max(Start_Date))"}>}f_engaged)
),'#,##0.#%'))

District24Sep2020-8Oct20209Oct2020-21Oct202022Oct2020-4Nov20205Nov2020-18Nov202019Nov2020 - 2Dec2020
D2----8%
D3-8%--10%
D410%----

 

Expected Output

District24Sep2020-8Oct20209Oct2020-21Oct202022Oct2020-4Nov20205Nov2020-18Nov202019Nov2020 - 2Dec2020
D240%60%70%80%8%
D325%8%30%20%10%

 

Can someone please help me with the issue.

@sunny_talwar @nagarjuna_kotha @DeepakVadithala 

Thanking in Advance.

Regards,
Jesh

9 Replies
edwin
Master
Master

why should D4 not be included when it is <=10%?

 

edwin
Master
Master

what is max(start_date) equal to?

Jesh19
Creator II
Creator II
Author

Start_Date is the start date of each Period

Also, we should show only the districts based on current period. so, only D2 & D3

edwin
Master
Master

take a look at this if it will help

edwin
Master
Master

assuming that the latest start date is your current period, you count back from it

normally, the user should be able to look at historical data, and will define what the current period is by selecting the start date and you count from there.  you then save the selected start date index to a variable and compare from that in your expression or set analysis.  but if the current period is static based on reload date then this should suffice

Jesh19
Creator II
Creator II
Author

Hi Edwin,

Thanks for the solution.

But, this doesn't suffice my requirement. 

edwin
Master
Master

check this out:

Jesh19
Creator II
Creator II
Author

Hi Edwin,

The solution that you suggested above is kind of showing top 5 records.

My requirement is entirely different. I should see the districts for last 5 periods which are having <=10% in the current period. This enables the user to analyze the trend as why the numbers went low in the current period compared to last 4 periods.

edwin
Master
Master

there is no logic to select top 5 records.  it is by coincidence that only the top 5 records satisfy the criteria for selection (<=10% in last 5 periods) which i thought was the requirement.  ive adjusted it to ensure more than 5 had <=10% in last 5 periods to show the solution does not limit to top 5