# 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
Did you mean:
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.#%')

 District 24Sep2020-8Oct2020 9Oct2020-21Oct2020 22Oct2020-4Nov2020 5Nov2020-18Nov2020 19Nov2020 - 2Dec2020 D1 50% 20% 30% 90% 45% D2 40% 60% 70% 80% 8% D3 25% 8% 30% 20% 10% D4 10% 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.#%'))

 District 24Sep2020-8Oct2020 9Oct2020-21Oct2020 22Oct2020-4Nov2020 5Nov2020-18Nov2020 19Nov2020 - 2Dec2020 D2 - - - - 8% D3 - 8% - - 10% D4 10% - - - -

Expected Output

 District 24Sep2020-8Oct2020 9Oct2020-21Oct2020 22Oct2020-4Nov2020 5Nov2020-18Nov2020 19Nov2020 - 2Dec2020 D2 40% 60% 70% 80% 8% D3 25% 8% 30% 20% 10%

Regards,
Jesh

9 Replies
Master

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

Master

what is max(start_date) equal to?

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

Master

take a look at this if it will help

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

Creator II
Author

Hi Edwin,

Thanks for the solution.

But, this doesn't suffice my requirement.

Master

check this out:

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.

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

Community Browser