Dynamical Expression

[Anonymous]

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.

[vishsaggi]

‌mmay be try this

iin your expression just before your maxstring give =

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

[Anonymous]

Hi Vish,

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

[chrismarlow]

Hello,

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

Regards,

Chris.

[Anonymous]

Hi Christopher,

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

[chrismarlow · Accepted solution]

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.

[vishsaggi]

Try this?

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

[Anonymous]

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

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

[Anonymous]

Thanks Chris. I got it following your exporession.

[Anonymous]

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:

1. From Jan to Aug 2016: Sum({< Year_month = {"<=$(=Date#(Max(Year_month),YYYY_MMM))"} >} Rev)
2. 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