topic Re: Dimension Selection in QlikView

Dimension Selection

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

Hello,

I have a data model with bunch of tables. One of them has the sample information:

ItemNo Date Handler Amount

1 2014-12-31 AA 200

1 2015-01-31 BB 100

1 2015-02-28 CC 100

2 2014-12-31 EE 200

2 2015-01-31 DD 100

The front end application is based on the user selecting the date and relevant charts showing information as of that date. For example, if user wanted to know Handler information and Amount on 2015-01-31 for all ItemNo, they would select the date and know handler to be BB for ItemNo1 with amount of 100 and DD for ItemNo2 with amount of 100 and so on.

However, given the selection is made, is there some way to show who the Handler was prior year end. So given selection of 2015-01-31, I want to show something like this in a chart for all ItemNo:

ItemNo Current Handler PriorYearEnd Handler CurrentAmount PriorYearEndAmount

1 BB AA 100 200

2 DD EE 100 200

I know how to use set analysis to get the amount for prior year end but not sure how to get the dimension(Handler) value at prior year end.

Appreciate any help!

Re: Dimension Selection

Gysbert_Wassenaar — Wed, 23 Mar 2016 08:26:50 GMT

See attached example.

Re: Dimension Selection

sunny_talwar — Wed, 23 Mar 2016 08:33:05 GMT

Check out the attached

Dimension: ItemNo

Expressions

1) =Handler

2) =Only({<Date>}Aggr(If(Max({<Date = {"$(='<' & Date(Max(Date)))"}>}TOTAL <ItemNo> Date) = Only({<Date>} Date), Only({<Date>}Handler)), ItemNo, Date))

3) Amount

4) =Only({<Date>}Aggr(If(Max({<Date = {"$(='<' & Date(Max(Date)))"}>}TOTAL <ItemNo> Date) = Only({<Date>} Date), Only({<Date>}Amount)), ItemNo, Date))

UPDATE: Now the question is what do you want to see when nothing is selected?

Re: Dimension Selection

shariqusmani — Sun, 27 Mar 2016 03:59:21 GMT

Thanks! This does help. I didn't know much about the Only function until now.

However, I must admit, for a newbie like me, I didn't understand all of the arguments in Expression 2). So to play around with it, I did something like this:

only({<Date = {'$(yearenddate)'}>} TOTAL <ItemNo> Handler)

where yearenddate is a variable that calculates the prior year end date based on the user's selection of the date. This seems to do the trick, but if I can bother you to 'translate' the arguments in your expression 2, that would be even more helpful.

Another question, that I would like to add: If in addition to Handler, I have a dimension called Status (Open/Close), and I wanted to filter the chart to show only Status of Open at prior year end, how would I go about doing this?

So the data would be something like this:

ItemNo Date Handler Amount Status

1 2014-12-31 AA 200 Open

1 2015-01-31 BB 100 Open

1 2015-02-28 CC 100 Closed

2 2014-12-31 EE 200 Closed

2 2015-01-31 DD 100 Closed

So based on this data, and selection of 2015-01-31, the chart should only show ItemNo 1 since at the prior year end, it was Open whereas ItemNo2 was closed.

As for your question, all my charts show condition only if getselectedcount(Date) = 1, so if the user does not select any date, no data is displayed, I have a text box, that will tell the user to select a date.

Re: Dimension Selection

sunny_talwar — Sun, 27 Mar 2016 11:32:03 GMT

but if I can bother you to 'translate' the arguments in your expression 2, that would be even more helpful.

Not sure what are you asking me to translate? Can you please elaborate on what you want?

Another question, that I would like to add: If in addition to Handler, I have a dimension called Status (Open/Close), and I wanted to filter the chart to show only Status of Open at prior year end, how would I go about doing this?

So the data would be something like this:

ItemNo Date              Handler Amount Status

1            2014-12-31    AA        200          Open

1            2015-01-31 BB          100          Open

1            2015-02-28 CC        100          Closed

2            2014-12-31 EE          200          Closed

2            2015-01-31 DD          100          Closed

So based on this data, and selection of 2015-01-31, the chart should only show ItemNo 1 since at the prior year end, it was Open whereas ItemNo2 was closed.

Introduction of new selection seems to be working very well with the existing data.

Re: Dimension Selection

shariqusmani — Sun, 27 Mar 2016 14:47:50 GMT

What I meant by translate, was if you can break down the syntax? Like what does it mean when we write:

Only({<Date>}

Aggr(If(Max({<Date = {"$(='<' & Date(Max(Date)))"}>}TOTAL <ItemNo> Date)

and so on...

With regards to status:

This will not work, if user selects 2015-02-28 as the status for that selection is Closed, and Open will be greyed out. But I want to show ItemNo1 as its status was Open at prior year end.

I tried putting something like

if(Status = 'Open', ItemNo) on the Dimension side, but this will not help in case I want to do YTD calculation, as doing this will omit the row for

1 2015-02-28 CC 100 Closed

in the chart, making the computation of YTD given a selection of that date, incorrect.

Thanks again for all your help!