topic Re: Qlikview Selection in QlikView

Qlikview Selection

ryanfoster84 — Sun, 06 Dec 2015 06:44:57 GMT

Hi,

i was wondering if someone could help i have the following piece of code

=If([Revenue Adjustment] = 0, 'Revenue Adjustments Not Included', 'Revenue Adjustments Included Up Through'& ' ' & Max([Year Month]))

so when the user select a month it will say if the adjustments have been loaded or not but i need to to say only the latest month which has been selected regardless of month selection can anyone assist. currently when you select a month it will say that month.

cheers,

ryan

Re: Qlikview Selection

sunny_talwar — Sun, 06 Dec 2015 13:01:31 GMT

Try this:

=If([Revenue Adjustment] = 0, 'Revenue Adjustments Not Included', 'Revenue Adjustments Included Up Through'& ' ' & Max({1} [Year Month]))

=If([Revenue Adjustment] = 0, 'Revenue Adjustments Not Included', 'Revenue Adjustments Included Up Through'& ' ' & Max({<MonthField>}[Year Month]))

First one won't change the Max([Year Month]) based on any selections you make in you data base, it will always show the Max. Second one just won't change based on selection in your MonthField. If for instance you make selection in YearField or another field such as Product, it will change. So based on your requirement you can decide which one better suits your needs.

Best,

Sunny

Re: Qlikview Selection

ryanfoster84 — Sun, 06 Dec 2015 13:23:37 GMT

hi thanks for that but unfortunately that is not what i am looking,

basically i need it to say the the latest month that has adjustments loaded. so if i make a date selection of September 2015 but there are adjustments loaded until November 2015 it will say Revenue Adjustments Included Up Through Nov 2015.

Re: Qlikview Selection

sunny_talwar — Sun, 06 Dec 2015 13:54:39 GMT

I believe that is what we are doing, right? We are trying to ignore selections made in the date field to always show the maximum date where revenue adjustments were made.

If I am still not thinking it right, would you be able to share a small sample?

Re: Qlikview Selection

ryanfoster84 — Sun, 06 Dec 2015 14:19:53 GMT

sadly i can't share the app as it contains customer data but here is a screen shot

it says 201509 but i need it to say October 2015 as this was the latest month to have adjustments.so currently at the minute we are i December and the adjustments for November will be loaded in the middle of the month. so once they are loaded it will then say Nov 15

Re: Qlikview Selection

ryanfoster84 — Sun, 06 Dec 2015 14:35:18 GMT

also

=If([Revenue Adjustment] = 0, 'Revenue Adjustments Not Included', 'Revenue Adjustments Included Up Through'& ' ' & Max({1} [Year Month]))

this is returning 201512 as its the max date in the date regardless of selection but i need it to bring 201510 as it is the latest month to adjustments but that will change once Novembers are loaded in the middle of December.

the second formula is just bring back the year month that you select

Re: Qlikview Selection

sunny_talwar — Sun, 06 Dec 2015 14:44:04 GMT

See if the attached sample makes sense:

Re: Qlikview Selection

ryanfoster84 — Sun, 06 Dec 2015 15:19:45 GMT

i see what you are doing and tried the following formula:

=If([Revenue Adjustment] = 0, 'Revenue Adjustments Not Included', 'Revenue Adjustments Included Up Through'& ' ' & MonthName(Max({1<[Revenue Adjustment] = {'*?'}>}[Year Month])))

but still didn't work

would it be because there are multiple values for 31/10/15.

appreciate the help

Re: Qlikview Selection

sunny_talwar — Sun, 06 Dec 2015 16:18:15 GMT

That should have not made any difference, because in the attached sample, I added another date for 10/03/2015 and still seems to work.

Re: Qlikview Selection

ryanfoster84 — Sun, 06 Dec 2015 16:40:38 GMT

yeah no luck at all very annoying

Re: Qlikview Selection

sunny_talwar — Sun, 06 Dec 2015 19:43:29 GMT

What expression are you using for Revenue Adjustment? It is just Sum?

Re: Qlikview Selection

ryanfoster84 — Mon, 07 Dec 2015 07:51:42 GMT

its a dimension that is summed in the extract script.

Re: Qlikview Selection

sunny_talwar — Mon, 07 Dec 2015 16:59:12 GMT

In one of the above posts, you added this screenshot:

You have year 2451 in your database or what is going on there?