Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
smilingjohn
Specialist
Specialist

Complications

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 .

8 Replies
Or
MVP
MVP

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.

smilingjohn
Specialist
Specialist
Author

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

Thanks

Or
MVP
MVP

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

smilingjohn
Specialist
Specialist
Author

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

smilingjohn
Specialist
Specialist
Author

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')..

Or
MVP
MVP

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.

smilingjohn
Specialist
Specialist
Author

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

Or
MVP
MVP

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