topic Disregarding a dimension in expression in QlikView

Disregarding a dimension in expression

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

Hello All,

This is my first post in the forum. I was asked a question in and interview and I'm still not able to figure out the answer. Here goes the question. A table has two fields - a year-month field (for simplicity & testing, I have used a 'date' field with value as the first date of a month in place of 'year-month'. eg: 01-2015 will be 01/01/2015 in my file) and Sales.

In the same table, I want to display beside the Sales field, the sales for next month. This has to be done as a Chart expression and not in script. My fields are [Month-Year], [Sales1]. [MOnth-Year] has a date instead of MM-YYYY as mentioned above. My set expression for getting the next month sales is:

sum(TOTAL{<[Month-Year]={"=$(=Date(AddMonths(Date(Date#([Month-Year],'DD/MM/YYYY'),'DD/MM/YYYY'),1),'DD/MM/YYYY'))"}>}Sales1)

I have used the 'TOTAL' keyword in the expression hoping it would disregard the dimension selections across the rows.

This expression does not seem to fetch the next month sales and returns 0.

Please let me know your solutions for this question.

Re: Disregarding a dimension in expression

sunny_talwar — Tue, 27 Mar 2018 13:56:15 GMT

May be this

Above(Sum(Sales1))

Also, read here

Missing Manual - Above() and Below()

Re: Disregarding a dimension in expression

vidhyamurali — Wed, 28 Mar 2018 04:23:25 GMT

Thank you. The detailed blog post about Above() and Below() functions were helpful too.

As an aside, is there a way to get this working using Set Analysis and disregard the dimensions?

Re: Disregarding a dimension in expression

sunny_talwar — Wed, 28 Mar 2018 13:56:32 GMT

Set analysis - No, but you can read about The As-Of Table which might be able to optimize your calculations