topic Re: Difference/Trend based on Month(s) field - Retention in QlikView

Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Wed, 25 Nov 2020 16:16:04 GMT

I have the following Data model with the fields:

Month

Customers

Month Customers

Jan 200

Feb 150

Mar 170

Apr 220

. .

Dec 80

I need to provide users option where the users can select any month or months and the difference between the selected month(s) and the remaining months should be populated.

For example, if the user selects Jan, the following output should be seen

Month Customers Difference

Jan 200 0

Feb 150 50

Mar 170 30

Apr 220 -20

. . .

Dec 80 120

The user should be able to pick any month or months instead of Jan, dynamically and based on the selected month, the Difference column should be calculated.

Re: Difference/Trend based on Month(s) field - Retention

devarasu07 — Thu, 04 Jan 2018 03:37:08 GMT

Hi,

Try to use above function,

create measure like below

alt(sum(Customers)-Above(Customers),0)

Re: Difference/Trend based on Month(s) field - Retention

pradosh_thakur — Thu, 04 Jan 2018 03:42:22 GMT

may be this

Re: Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Thu, 04 Jan 2018 03:42:39 GMT

My requirement is a bit different.

The User should be able to pick a month and the difference should be based on the selected month.

For example, if the user selects March, the difference for all the months should be (March ~ Individual Months)

Re: Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Thu, 04 Jan 2018 04:47:12 GMT

Hi,

Thank you so much. Can we do the same thing for multiple months? In case, if the user selects two or months, for example, Jan and Feb, the result should be (Jan + Feb) ~ each individual remaining months.

Re: Difference/Trend based on Month(s) field - Retention

pradosh_thakur — Thu, 04 Jan 2018 04:51:19 GMT

yes.

month filter has always one selected value . uncheck that and select multiple months , it shall work.

but let me warn you i have used alternate states here. you can do the other way around and use table in alternate state and filter in inherited state.

Re: Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Thu, 04 Jan 2018 04:58:39 GMT

Selecting multiple months is not giving the right numbers in the result column. Any changes needed for the expression?

Re: Difference/Trend based on Month(s) field - Retention

pradosh_thakur — Thu, 04 Jan 2018 05:30:02 GMT

this is what i am getting . feb+jan - months . is the requiremnt diffrent. if so please post the expected output

Re: Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Fri, 05 Jan 2018 21:00:44 GMT

The state works for once and gives correct values soon after. But when I change the sheet, reload data or close and open the file, the state isn't working.

Re: Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Mon, 08 Jan 2018 02:47:43 GMT

The Alternate state gives out the result column when created. But later doesn't give the values which are correct. Strange. Any reason why it is behaving this way

Re: Difference/Trend based on Month(s) field - Retention

pradosh_thakur — Mon, 08 Jan 2018 07:57:12 GMT

please explain the issue.

Re: Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Mon, 08 Jan 2018 11:54:55 GMT

When I create the alternate state, assign it to the list box, the values in the column show up correct. But after I reopen the document, or add other objects or even try to change the sheet and come back, the column shows incorrect values.

Re: Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Mon, 08 Jan 2018 14:28:50 GMT

As soon as I create the alternate state, assign it to the list box and create an expression in the chart, the resultant column shows right values. But once I make other selections or clear selections and make the same selections in the list box, the values are messed up. I have to create a new alternate state and assign it again in order to see the correct values. Even that shows the same issue after clearing out the selections and picking them again.

Re: Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Mon, 08 Jan 2018 16:20:23 GMT

Pradosh, I have another column called Users that contains all the Users.

My Requirement is

1. When I select the month, I should see the values in the column only for the subsequent months. Meaning, if I select Mar, I should see the result values from Mar or Apr but not the previous months.

2. I also need to check what users are present in the Selected month and the remaining months and show the Result.

For example, when March is selected, the resultant column should show only those Users that are common in March and the remaining months respectively.

Re: Difference/Trend based on Month(s) field - Retention

qlikwiz123 — Mon, 08 Jan 2018 18:07:19 GMT

Pradosh, I have another column called Users that contains all the Users.

My Requirement is

1. When I select the month, I should see the values in the column only for the subsequent months. Meaning, if I select Mar, I should see the result values from Mar or Apr but not the previous months.

2. I also need to check what users are present in the Selected month and the remaining months and show the Result.

For example, when March is selected, the resultant column should show only those Users that are common in March and the remaining months respectively.