need some help with the logic

[shree909]

Hi folks i have a question

i have a month table  for 12 months

and i have  four kpis for each month like jan_actual, jan_forecast,jan variance,jan_variance_% for 12 months

i want to ceate a straight table in sucha way that

it has first 4 kpis which are same  for 12 months

and when i select the jan it has to show the first 4 kpis and followed by jan 4 kpis,\

and when i slect jan , feb then it has to shoow jan 4 kpis, feb 4 kpis.... and so on..

can someone help me...

[Gysbert_Wassenaar]

I think I would solve this in the load script by transforming the data using the crosstable function so it is in the form of a table with the month_kpi fields transformed into month, kpi, value.

An alternative is using for variables for the four month kpi. The expressions in the table are set to $(vActual), $(vForecast) etc. and the variables are of the form pick(num(Month),'[jan_actual]','[feb_actual]',...,'[dec_actual]'). So choosing a month will pick the matching month-kpi field with the pick function.

[shree909]

thanks but not working correct me if i am wrong,

i am using in the expressions...and if i select two months it should sum up and show the result

=

pick((Month),$(vJan_Actuals))

[IAMDV]

Please post your QVW file and I think you should use Crosstable instead of solving on UI.

Thanks,

DV

www.QlikShare.com

[Gysbert_Wassenaar]

See attached example. I've only added six months worth of expressions in the upper chart. I first thought the lower chart was what you wanted.

[IAMDV]

Thanks Gysbert. I think this is what Shree needs. However, I feel uncomfortable using column selections unless REALLY required because of the performance issues. Did you notice the charts Calc Time: 92 and taking 512kb memory for this single object. Also, I'm taking your expression a step further because - No Selections in QlikView means Select All but it's different in Human Brain. I guess this is where QV thinks differently from Human Brain .

I'd use this kind of expressions in conditional expressions.

=IF(GetSelectedCount(Month) > 0, wildmatch(getfieldselections(Month,' ',12),'*jan*') > 0, 0)

Good luck Shree909!

Cheers,

DV

www.QlikShare.com

[Gysbert_Wassenaar]

Oi Deepak,

I did some testing with more data and a version where I transformed the data with crosstable using a pivot table to show the data. The straight tables on the original data seem to outperform the pivot table. I didn't expect that. But it looks like the conditional display expressions don't add so much calculation overhead that the pivot table becomes the faster option. The crosstable transformed data also results in a bigger qvw document and more memory usage of the chart objects.

About GetSelectedCount, I guess that's a matter of personal preference regarding design. Since in Qlikview no selection means select all, not using GetSelectedCount means you stay consistent with the way Qlikview works by default. And I think there's something to be said for keeping the user experience consistent.

regards,

GW

[IAMDV]

Many thanks Gysbert. I'm very keen to see the results of your tests. Please can you share the QVW file? This is should be very interesting in terms of understanding how QlikView calculates. Did you check if all the cores are used while calculating these expressions? Also, I'm interested to understand how caching works when we enable & disable the conditional experssions.

Totally agreed on GetSelectedCount. It's better to keep it consistent but I thought it will be useful for business users who don't understand this pattern. I had received loads of questions about selecting all the field values. In some cases I had seen applications where they had a value "ALL" and you can select the field value.

Once again appreciate your time for doing these tests and I like your contributions on QlikCommunity.

Cheers,

DV

www.QlikShare.com

[Gysbert_Wassenaar]

Oi Deepak,

See attached zip file. I had to remove most of the data from the excel file to be able to compress it to a reasonable size for uploading here. You can use the formulas in E2:BD2 to generate random data so the other 65K records are filled.

regards,

GW

[shree909]

thanks for the reply..

can u tell me why its taking more time to load the chart when i select 2 or 3 months..

is there a way if i select jan, mar

the chart should display as calucated actual and forcast from jan to march

something like this,,,