topic Re: Complications in QlikView

Complications

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

Hi Expertise,

Please find the attachment of the QVW file.

I have a table chart where there is a average of three years and this depends on the load date selection .

Currently iam selecting say october -2017(10-10-2017 from load date then my chart should show the 2015 AVG and 2016 avg and 2017 avg only till october..

it should show devide 2017/ 10 ..

This should be dynamic ...

everything should show based on load date selection .

Re: Complications

Or — Wed, 08 Nov 2017 07:55:30 GMT

It would be easier to do this, generally speaking, if you isolate the month and year for each date at the script level. This simplifies the set analysis later:

[Load Date] =,[Load Year] = {$(=SELECT_YEAR-1)} , [Period Number] = { "<=$(=MONTH_NUM)"}

Note that in this example I've used variables for SELECT_YEAR, which evaluates to Only([Load Year]), and MONTH_NUM, which evaluates to num([Load Month]). This isn't strictly necessary but I find it easier to read.

You could take the same approach without loading the month and year at the script level, by isolating them using the variables.

Re: Complications

smilingjohn — Wed, 08 Nov 2017 08:58:25 GMT

HI Shoham can you please involve this into my QVW file...Will be really helpfull

Thanks

Re: Complications

Or — Wed, 08 Nov 2017 09:15:13 GMT

Nope. I can't run your script, which means I can't do anything in your file.

Re: Complications

smilingjohn — Wed, 08 Nov 2017 09:22:47 GMT

if the created date is

=Pick(Dim,MonthName(Created),'AVG 2015','AVG 2016','AVG 2017')

I want this t be dynamic ..i dnt want to change this for next year

How do i achive this

Re: Complications

smilingjohn — Wed, 08 Nov 2017 09:32:07 GMT

Shoham

is there way to make

to make this dimesion as dynamic .....if this can be achived as dynamic then my thing is achived ...

=Pick(Dim,MonthName(Created),'AVG 2015','AVG 2016','AVG 2017')..

Re: Complications

Or — Wed, 08 Nov 2017 09:49:10 GMT

You could probably use:

=Pick(Dim,MonthName(Created),Year(LoadDate)-2 , Year(LoadDate)-1, Year(LoadDate))

But it'd be extremely slow. It's not really a healthy way to create a dimension.

A more resource/speed friendly approach would be to create a variable called vYear, containing the following formula:

=Year(LoadDate)

And your calculated dimension would be:

=Pick(Dim,MonthName(Created),$(vYear)-2 , $(vYear)-1, $(vYear))

As for adding the 'AVG ' string aspect, I can't think of a good way to make this dynamic without making the code fairly clunky, as adding a text string will break your sorting.

Re: Complications

smilingjohn — Wed, 08 Nov 2017 11:17:19 GMT

I tried doing this witought adding the load date ... but this logic in Dimension shows only 2017 ..

=Pick(Dim,MonthName(Created),Year(Created)-2,Year(Created)-1,Year(Created))

do i need to change anything in this ?

This should give me 2015 , 2016 , 2017 and then monthname of 2017

but this is giving me only 2017 and the relaetd mons for 2017

Thanks

Re: Complications

Or — Wed, 08 Nov 2017 13:13:09 GMT

That doesn't look like it would work. Is there a problem with the approach I suggested?