topic Re: Dynamical Expression in QlikView

Dynamical Expression

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

Hello,

I have a pivot table in which I want to get the sum by month and then do the total from the beginning until the time of selection.

Eg: if I select 2016_Aug => all the expression from 2015_Jan to 2016_Aug will pop up. Simultaneously the column Total to date will show the accumulation accordingly. (Transpose approach to turn Time into dimension is not relevant as it will affect multiple parameters later)

I want to explore if it is possible and how to do the Total by Set Analysis. There is something wrong with my expression as it doesn't return correct result.

sum({<Year_month={"<$(maxstring(Date#(Year_month,YYYY_MMM)))"}>}Rev)

I'm very new to Qlik and would like to collect tips from you.

Re: Dynamical Expression

vishsaggi — Mon, 03 Sep 2018 18:31:35 GMT

‌mmay be try this

iin your expression just before your maxstring give =

i mean like “<$(=maxstring(Date(year_month,’YYYY_MMM’)))”

Re: Dynamical Expression

Anonymous — Mon, 03 Sep 2018 18:56:13 GMT

Hi Vish,

Just tested. It doesn't work. Wonder if it might be related to the format date.

Re: Dynamical Expression

chrismarlow — Mon, 03 Sep 2018 19:14:49 GMT

Hello,

Is your field Year_Month being stored as a date? Or is it interpreting as text?

Regards,

Chris.

Re: Dynamical Expression

Anonymous — Mon, 03 Sep 2018 19:17:52 GMT

Hi Christopher,

It is a created field, stored in format 'YYYY_MMM', as in the script.

Re: Dynamical Expression

chrismarlow — Mon, 03 Sep 2018 19:45:39 GMT

I think the issue is your script is storing as text, rather than a date formatted YYYY_MMM, but I can't open your QVW, so I cannot see. Is the script showing Date([name of the date field],'YYYY_MMM') AS Year_month?

A solution that might work if you cannot change the formatting (although not sure will work exactly how you expect, as maybe I misunderstand what you mean by 'selection' ...) is;

sum({<Year_month=>}If(Date#(Year_month,'YYYY_MMM')<=Date#(GetFieldSelections(Year_month),'YYYY_MMM'),Rev,0)).

Regards,

Chris.

Re: Dynamical Expression

vishsaggi — Mon, 03 Sep 2018 20:29:45 GMT

Try this?

= Sum({< Year_month = {"<$(=Date#(Max(Year_month),YYYY_MMM))"} >} Rev)

Re: Dynamical Expression

Anonymous — Tue, 04 Sep 2018 02:03:11 GMT

Thanks Vishwarth. Only a small adjustment. Otherwise it only return until the previous month:-)

= Sum({< Year_month = {"<=$(=Date#(Max(Year_month),YYYY_MMM))"} >} Rev)

Re: Dynamical Expression

Anonymous — Tue, 04 Sep 2018 02:04:03 GMT

Thanks Chris. I got it following your exporession.

Re: Dynamical Expression

Anonymous — Tue, 04 Sep 2018 05:01:33 GMT

Hi Vishwarath,

In case I want to calculate for the same period of previous year, can you suggest how I can leverage on your expression?

Like, if I choose 2016_Aug, I will have output of 2 expressions:

From Jan to Aug 2016: Sum({< Year_month = {"<=$(=Date#(Max(Year_month),YYYY_MMM))"} >} Rev)
From Jan to Aug 2015:

I think addyears(-1) may help but don't know how to nest it into the expression correctly.

Thank you