Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
smilingjohn
Creator III
Creator III

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
Master
Master

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
Creator III
Creator III
Author

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

Thanks

Or
Master
Master

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

smilingjohn
Creator III
Creator III
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
Creator III
Creator III
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
Master
Master

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
Creator III
Creator III
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
Master
Master

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